Query/400 Finds the Larger of Two Numbers
We had a request from a user who wanted a Query/400 report that showed the greater of average monthly usage and minimum order quantity. Query/400 does not include conditional (if-then-else) statements, but you can make it work through simple mathematical functions. Here’s how:
1. Under Specify processing options, set the Use Rounding option to N.
2. Define a result field that finds the difference between the two numbers, which I’ll refer to as A and B. See the calculation for field DIFF in Figure 1. If A is greater than B, DIFF is positive; otherwise, DIFF is negative.
3. Define a one-digit field with zero decimal positions. In Figure 1, this is the FACTOR field. If DIFF is negative, the numerator is greater than the denominator, and the result is more than 1. If DIFF is positive, the numerator is less than the denominator, and the result is less than 1. The 0.0001 ensures that there’s no divide by zero if A is equal to B. Because the rounding has been turned off, the result is 0 if A is greater than B, or 1 if B is greater than A.
4. Define a final result field (LARGER in Figure 1) that will be the greater of the two numbers. Essentially, you’re turning off either A or B by multiplying the lower number by zero.
To find the smaller of two numbers, just switch A with B and B with A in step 4.
—Bob Ellsworth
Pollak Engineered Products
Editor’s Note: For more Query/400 tips, look for Bob Ellsworth’s article coming in the October 2000 issue of Midrange Computing.
Field Expression
DIFF A - B
FACTOR ((DIFF - 0.001) * (DIFF - 0.001)) / ((DIFF * DIFF) + 0.0001)
LARGER (A * (-1)) * (FACTOR - 1)) + (B * FACTOR)
Figure 1: Define these result fields to make Query/400 find the larger of two numbers.
Controlling Query/400 Runtime and Output Options
When Query/400 was opened up to users throughout our organization, we started experiencing problems with large files being created. So I started a search to find a way to control the size of a file created by a query (the default is *nomax). Thanks to our IBM Business Partner and a contact at IBM, I received the following information about controlling Query/400 queries.
Query/400 scans the library list for a data area named QQUPRFOPTS. If this data area exists, Query/400 retrieves runtime options from positions 2 through 75, as described in Figure 2 (page 120). If this data area is not found, or if any parameter values are left blank in the data area, the default values are used.
The SBMJOB parameters in the Purpose column refer to the 8=Batch option from the Work with Queries display.
If you want to use this data area, you’ll have to create it yourself. Use the CL command in Figure 3 (page 120) as a guide, replacing xxx with the name of the library to contain the data area.
Figure 4 (page 120) illustrates how you might also use this command to change the default size of a Query/400 output file. The file will be created with 50,000 initial records, with an increment of 5,000 records, and allow up to 10 increments.
—Bruce Nyberg Candle Corporation of America
Position Purpose Options
1 Future use
2 RTGDTA for SBMJOB ‘B’ = QCMDB (QBATCH)
‘ ‘ = *JOBD
3 OUTQ for SBMJOB ‘J’: *JOBD (default)
‘C’: *CURRENT ‘U’: *USRPRF
‘D’: *DEV
4 PRTDEV for SBMJOB ‘J’: *JOBD (default)
‘C’: *CURRENT ‘U’: *USRPRF
‘S’: *SYSVAL
5 INQMSGRPY for SBMJOB ‘J’: *JOBD (default)
‘R’: *RQD ‘D’: *DFT ‘S’: *SYSRPYL
6-15 JOB for SBMJOB 10-character name associated with the batch job
*JOBD: Use the job description *QRY: Use the query definition
16 - 35 JOBD for SBMJOB 10-character job description
+ 10-character library *USRPRF—Use the job description from the user profile
36 - 55 JOBQ for SBMJOB 10-character job queue name + 10-character library
*JOBD—Use the job description from the user profile
56 - 65 Initial number of records 10,0 zoned decimal number from 1 through 2147483646 for the output file
66 - 70 Increment number of 5,0 zoned decimal number from 0 through 32767 records for the output file
71 - 75 Maximum number of 5,0 zoned decimal number from 0 through 32767 increments for the output file
Figure 2: Query/400 can retrieve runtime options from data area QQUPRFOPTS.
CRTDTAARA DTAARA(xxx/QQUPRFOPTS) +
TYPE(*CHAR) LEN(80) AUT(*USE) +
TEXT(‘Query/400 user profile options’)
CHGDTAARA DTAATA(xxx/QQUPRFOPTS (56 20)) +
VALUE('00000500000500000010')
Figure 3: Use this CL command to create the QQUPRFOPTS data area.
Figure 4: Use CHGDTAARA to specify runtime query options.
Unscrew the Lid
If you’re new to Java programming, and most of us are, then some of the concepts and terms may be unfamiliar to you. One of those terms you may not be familiar with is the Java ARchive (JAR) file, which is nothing more than a repository of compressed Java classes (programs and objects) that make up a particular “toolbox.” For example, the AS/400 Toolbox for Java has many JAR files, such as JT400.jar and UTIL400.jar. These JAR files contain classes that have already been created by other Java programmers, IBM programmers in this case, to provide you with prewritten objects, or tools, which you can use to help build your own Java programs. For example, the Java class AS400 is in the JT400.jar file. This is the class you would use in your own Java program to create a logon to the AS/400.
JAR files can seem ominous, since it’s hard to see what is inside them. But don’t despair; there’s a way to open a JAR file and peek inside. Use a utility, such as WINZIP or PKZIP, to open the JAR file and look inside. I’ve found that this makes programming in Java easier, since I can visually scan for a class, which, based on its name, appears to perform the function that I need. Then it’s a simple matter to read the Java documentation about that particular class to verify that it actually does what you think it should. The alternative is to manually dig through IBM’s AS/400 Toolbox for Java documentation, searching for classes that do what I need. That’s a process that is made unnecessarily difficult because the online HTML documentation lacks a SEARCH feature.
—Shannon O’Donnell Senior Technical Editor
Midrange Computing
Right-justification with SQL
Q: I need to right-justify a character field in SQL, as I can do with EvalR in RPG IV. Is this possible?
—David Christie
A: Yes, the SQL SELECT statement in Figure 5 illustrates how to do this. Make sure that the first argument of the substring operation has as many blanks as the length of the field.
—Bill Robins
select substr(' ',1,length(charfld) - length(trim(charfld))) || trim(charfld)
from filename
Figure 5: SQL can right-justify character fields.
Universal CL Comment Delimiter
The string /*/ can begin and end CL comments. It’s useful for commenting out lots of CL code. It can be assigned to a single key combo for quick pasting.
For commenting an entire line at a time, I created a Client Access macro called COMMENT.MAC and assigned it to Ctrl-Shift-C. On an 80-column screen, it puts /*/ at the beginning of a line (assuming you start there) and also in the last 3 positions, then it goes to the start of the next line. The macro’s source is shown in Figure 6.
—Ken Rokos
Description =Comment CL source
[wait app]
"/*/
[backtab]
[backtab]
[backtab word]
[fast left]
[fast left]
[fast left]
[right]
[down]
"/*/
[tab field]
Figure 6: The COMMENT.MAC macro comments out entire lines of CL.
Wild-card Searches over Numeric Fields
You may know that the DB2 UDB query engine can perform a wild-card search over character fields, but did you know that it can do the same for numeric fields?
The trick is to convert the numeric field to a character field and strip out the leading For example, users tend to remember pieces of a dollar amount, especially the cents. Figure 7 shows the OPNQRYF (Open Query File) and SQL/400 commands that retrieve records with dollar amounts and with a cents portion of 95. The query engine retrieves amounts like $.95, $149.95, and $10.95.
Suppose users know that a payment was received in the amount of $11,793.00. They also know that a fee was probably deducted from this amount and the rest credited to a customer. They can pass to OPNQRYF a wild-card value of 117??00*.
—Alan A. Urtubia
OPNQRYF FILE((GLJRNL)) +
QRYSLT('%STRIP(%DIGITS(GJAMT) "0" *LEAD) *EQ %WLDCRD("*95")')
select * from gljrnl
where strip(digits(gjamt), leading, '0' ) like '%95'
zeros.
Figure 7: These OPNQRYF and SQL/400 commands search a numeric field for a wild-card argument.
Applying an Edit Mask to a Character Field
Q: I have a 60-character input/output field in a display file and would like to protect specific areas within this field from user input. Any ideas?
—Alan A. Urtubia
A: If the specific locations you want to protect within the 60-byte field are constant, write two DDS screens. The first screen contains input fields in nonprotected areas. The second screen specifies CLRL(*NO) and overlays input fields and adjacent protected areas with the entire 60-byte output-only field. The cursor will behave like you want it to.
—Gene Gaunt
That’s exactly what I was looking for. Figures 8 and 9 contain DDS and RPG source code, showing how I implemented your suggestion. I owe you one!
—Alan A. Urtubia
*===============================================================
* To compile:
*
* CRTDSPF FILE(XXX/UW218DS) SRCFILE(XXX/QDDSSRC)
*
*===============================================================
A DSPSIZ(24 80 *DS3)
A CA03(03)
A R DSP01
A 1 32'Select for Display'
A DSPATR(HI)
A 3 2'Type your selection and press Ente-
A r.'
A COLOR(BLU)
A 5 3'Customer'
A DSPATR(HI)
A 5 20'1 ... ... 2 ... ... 3 ... ... 4 ..A . ... 5 ... ... 6 ... ... 7'
A DSPATR(HI)
A FLD001 15A B 6 3
A FLD002 7A I 6 20
A FLD003 3A I 6 29
A FLD004 5A I 6 33
A FLD005 4A I 6 40
A FLD006 4A I 6 45
A FLD007 1A I 6 50
A FLD008 4A I 6 52
A FLD009 1A I 6 58
A FLD010 1A I 6 60
A FLD011 1A I 6 63
A FLD012 10A I 6 66
A FLD013 4A I 6 77
A 22 68'F3=Exit'
A COLOR(BLU)
A R DSP02
A CLRL(*NO)
A FLD014 61 O 6 20
*===============================================================
* To compile:
*
* CRTBNDRPG PGM(XXX/UW218RG) SRCFILE(XXX/QRPGLESRC)
*
*===============================================================
FUW218DS CF E WORKSTN
C Eval Fld014 = *All'*'
C DoU *Inkc = *On
C Write Dsp01
C Write Dsp02
C Read Dsp01
C EndDo
C Eval *Inlr = *ON
Figure 8: Display file UW218DS defines two record formats for the same area of the display.
Figure 9: RPG program UW218RG overlays an output-only field with input-capable fields.
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online