Information Systems:Query/400
Note: Whereas Query/400 may have been easier to use for green screen aficionados, it is much easier to take 10 minutes to learn basic SQL instead, and to use the 'Run SQL Scripts' tool of IBM Access Client Solutions to run your statements. The article below is useful for dealing with existing/saved queries done in Query/400, but for new queries (regardeless of it is simple or complicated, ad-hoc or meant to be reused), good god just learn raw SQL and save yourself the trauma. -norwizzle (talk)
Query
‘Query’ is a tool to make an SQL call easier to define, and to use. SQL (Structured Query Language) is a special purpose programming language designed for managing data held in a relational database management system (according to Wikipedia). However, ‘Query’ only uses the input functions; not updating or deleting.
On a command line in production ASW (so that you have the correct library list), key in WRKQRY and press enter.
Work with Queries
Type choices, press Enter.
Option . . . . . . 1 1=Create, 2=Change, 3=Copy, 4=Delete
5=Display, 6=Print definition
8=Run in batch, 9=Run
Query . . . . . . . test1 Name, F4 for list
Library . . . . . QTEMP Name, *LIBL, F4 for list
F3=Exit F4=Prompt F5=Refresh F12=Cancel
(C) COPYRIGHT IBM CORP. 1988
Key in 1 to create a query, give it a meaningful name, and a library. If this is for production, use XX2480BP; for your own use, put it in your work library; if it is one time only, use QTEMP (which will be removed when you log off). Press enter.
Define the Query
Query . . . . . . : TEST1 Option . . . . . : CREATE
Library . . . . : QTEMP CCSID . . . . . . : 65535
Type options, press Enter. Press F21 to select all.
1=Select
Opt Query Definition Option
1 Specify file selections
_ Define result fields
_ Select and sequence fields
_ Select records
_ Select sort fields
_ Select collating sequence
_ Specify report column formatting
_ Select report summary functions
_ Define report breaks
_ Select output type and output form
_ Specify processing options
F3=Exit F5=Report F12=Cancel
F13=Layout F18=Files F21=Select all
This is a list of the options you can use to build your query. Key a ‘1’ in front of the option you want. You can select all the options you want – at one time, or select them one at a time.
Note that I am prefixing field names with the file ID. This can be done when you select more than one input file, but only must be done then two files have the same field name.
All the screens have ‘help’ (alt-F1). If there isn’t enough space on the screen for everything you want, you can press page down.
Specify file selections
Specify File Selections
Type choices, press Enter. Press F9 to specify an additional
file selection.
File . . . . . . . . . XXITEMP Name, F4 for list
Library . . . . . . *LIBL Name, *LIBL, F4 for list
Member . . . . . . . . *FIRST Name, *FIRST, F4 for list
Format . . . . . . . . *FIRST Name, *FIRST, F4 for list
File ID . . . . . . . T01 A-Z99, *ID
File . . . . . . . . . srosro Name, F4 for list
Library . . . . . . *LIBL Name, *LIBL, F4 for list
Member . . . . . . . . *FIRST Name, *FIRST, F4 for list
Format . . . . . . . . *FIRST Name, *FIRST, F4 for list
File ID . . . . . . . T02 A-Z99, *ID
F3=Exit F4=Prompt F5=Report F9=Add file
F12=Cancel F13=Layout F24=More keys
Key in the name of the file you want to use, clear the library (this will default to *LIBL, which will use your library list to find the file; which is why you run this from production ASW), and press enter. You can press enter again, until you get back to the list of options, or press F9 to add another file (if, for example, you want to see item numbers, descriptions, and on hand quantities). Press enter.
Specify Type of Join
Type choice, press Enter.
Type of join . . . . . . . . 2 1=Matched records
2=Matched records with primary file
3=Unmatched records with primary file
F3=Exit F5=Report F10=Process/previous
F12=Cancel F13=Layout F18=Files
‘2’ is the best choice here. It will include every record in the first file, whether or not there is a matching record in the second file. You can then select which records you want to use. Press enter.
Specify How to Join Files
Type comparisons to show how file selections are related, press Enter.
Tests: EQ, NE, LE, GE, LT, GT
Field Test Field
T01.ITPRDC EQ T02.SRPRDC_____
______________ _____ _______________
______________ _____ _______________
______________ _____ _______________
______________ _____ _______________
______________________________________________________________________________________
Field Text Len Dec
T01.ITPRDC Item # 35
T01.ITDES1 Description 1 50
T01.ITDES2 Description 2 50
T01.ITDESC Full Description 100
T01.ITTYPE Type 1
More...
F3=Exit F5=Report F10=Process/previous F11=Display names only
F12=Cancel F13=Layout F18=Files F24=More keys
On the top part of this screen you define the link between the two files; in this case item number. You can position your cursor to the bottom part of the screen, and press page down to see all the fields on both fields. Press enter.
Define Result Fields
Define Result Fields
Type definitions using field names or constants and operators, press Enter.
Operators: +, -, *, /, SUBSTR, ||, DATE...
Field Expression Column Heading Len Dec
VALUE T02.SRSTHQ * T02.SRAPCO ___________________ __ __
_________________________________ ___________________
_________________________________ ___________________
_________________________________ ___________________
_________ _________________________________ ___________________ __ __
_________________________________ ___________________
_________________________________ ___________________
_________________________________ ___________________
________________________________________________________________________________
Field Text Len Dec
T02.SRSTHQ Quantity on hand 15 3
T02.SRTSHQ Transit stock on hand 15 3
T02.SRPURQ Quantity on purchase order 15 3
T02.SRRETQ Quantity on return order 15 3
More...
F3=Exit F5=Report F9=Insert F11=Display names only
F12=Cancel F13=Layout F20=Reorganize F24=More keys
On the top of the screen, you can key in a calculation; in this case, the value is the on hand quantity times the average cost. Again, you can position your cursor to the bottom part of the screen, and press page down to see all the fields on both fields. Press enter.
Select and Sequence Fields
Select and Sequence Fields
Type sequence number (0-9999) for the names of up to 500 fields to
appear in the report, press Enter.
Seq Field Text Len Dec
10 T01.ITAGRP Account Group 4
20 T01.ITTYPE Type 1
30 T01.ITPRDC Item # 35
40 T01.ITDESC Full Description 100
50 T01.ITSIZE Size 15
60 T01.ITRESP Buyer 10
70 T02.SRSROM Warehouse number 3
80 T02.SRSTHQ Quantity on hand 15 3
90 T02.SRAPCO Average purchase cost 17 4
100 VALUE T02.SRSTHQ * T02.SRAPCO 31 7
T01.ITDES1 Description 1 50
T01.ITDES2 Description 2 50
F3=Exit F5=Report F11=Display names only F12=Cancel
F13=Layout F20=Renumber F21=Select all F24=More keys
Key in a sequence number for the fields you want to include. Page down to see all the fields available. Press enter.
Select Records
Select Records
Type comparisons, press Enter. Specify OR to start each new group.
Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...
AND/OR Field Test Value (Field, Number, 'Characters', or ...)
T02.SRSTHQ NE 0_____________________________
AND T02.SRSROM EQ 'MAI'_________________________
____ ______________ ____ ______________________________
____ ______________ ____ ______________________________
____ ______________ ____ ______________________________
____ ______________ ____ ______________________________
__________________________________________________________________________________
Field Text Len Dec
T01.ITAGRP Account Group 4
T01.ITTYPE Type 1
T01.ITPRDC Item # 35
T01.ITDESC Full Description 100
T01.ITSIZE Size 15
F3=Exit F5=Report F9=Insert F11=Display names only
F12=Cancel F13=Layout F20=Reorganize F24=More keys
Define which records are to be included. Here I am saying all items with quantities in the Main warehouse.
Select Sort Fields
Select Sort Fields
Type sort priority (0-999) and A (Ascending) or D (Descending) for
the names of up to 32 fields, press Enter.
Sort
Prty A/D Field Text Len Dec
1 A T01.ITAGRP Account Group 4
2 D VALUE T02.SRSTHQ * T02.SRAPCO 31 7
___ _ T01.ITTYPE Type 1
___ _ T01.ITPRDC Item # 35
___ _ T01.ITDESC Full Description 100
___ _ T01.ITSIZE Size 15
___ _ T01.ITRESP Buyer 10
___ _ T02.SRSROM Warehouse number 3
___ _ T02.SRSTHQ Quantity on hand 15 3
___ _ T02.SRAPCO Average purchase cost 17 4
F3=Exit F5=Report F11=Display names only F12=Cancel
F13=Layout F18=Files F20=Renumber F24=More keys
This will show items in order by item account group, and within that, by highest value first. Press enter.
Specify Report Column Formatting
Specify Report Column Formatting
Type information, press Enter.
Column headings: *NONE, aligned text lines
Column
Field Spacing Column Heading Len Dec Edit
T01.ITAGRP 0 Account 4 __
Group___________________
________________________
T01.ITTYPE 2 Type 1 __
________________________
________________________
T01.ITPRDC 2 Item # 8 __
________________________
________________________
F3=Exit F5=Report F10=Process/previous F12=Cancel
F13=Layout F16=Edit F18=Files F23=Long comment
You can change how the display, or report, will look. Here, I changed the heading from ‘VALUE’ to ‘Value’, and made the field lengths smaller. As Value was the result of a calculation, its field length and number of decimals was based on the on hand and average cost fields. It will never be that large, so it can be shortened for the report. ASW defines the item number field as 35 characters long, but we only use 8. Press enter.
Select Report Summary Functions
Select Report Summary Functions
Type options, press Enter.
1=Total 2=Average 3=Minimum 4=Maximum 5=Count
---Options--- Field Text Len Dec
_ _ _ _ _ T01.ITAGRP Account Group 4
_ _ _ _ _ T01.ITTYPE Type 1
5 _ _ _ _ T01.ITPRDC Item # 8
_ _ _ _ _ T01.ITDESC Full Description 100
_ _ _ _ _ T01.ITSIZE Size 15
_ _ _ _ _ T01.ITRESP Buyer 10
_ _ _ _ _ T02.SRSROM Warehouse number 3
_ _ _ _ _ T02.SRSTHQ Quantity on hand 15 3
_ _ _ _ _ T02.SRAPCO Average purchase cost 17 4
1 4 _ _ _ VALUE T02.SRSTHQ * T02.SRAPCO 9 2
F3=Exit F5=Report F10=Process/previous F11=Display names only
F12=Cancel F13=Layout F18=Files F23=Long comment
I am saying that I want a count of how many items are on the list, the total value, and the highest individual total.
Define Report Breaks
Define Report Breaks
Type break level (1-6) for up to 9 field names, press Enter.
(Use as many fields as needed for each break level.)
Break Sort
Level Prty Field Text Len Dec
1 10 T01.ITAGRP Account Group 4
_ T01.ITTYPE Type 1
_ T01.ITPRDC Item # 8
_ T01.ITDESC Full Description 100
_ T01.ITSIZE Size 15
_ T01.ITRESP Buyer 10
_ T02.SRSROM Warehouse number 3
_ T02.SRSTHQ Quantity on hand 15 3
_ T02.SRAPCO Average purchase cost 17 4
_ 20 VALUE T02.SRSTHQ * T02.SRAPCO 9 2
F3=Exit F5=Report F10=Process/previous F11=Display names only
F12=Cancel F13=Layout F18=Files F23=Long comment
I want totals by item account group; as well as final totals. Press enter until back to the list of options.
Special Output Type and Output Form
This is how to create a data file, which can be used as input to another Query, or to create a CSV file.
Select Output Type and Output Form
Type choices, press Enter.
Output type . . . . . . . . . . . 3 1=Display
2=Printer
3=Database file
Form of output . . . . . . . . . . 1 1=Detail
2=Summary only
Line wrapping . . . . . . . . . . N Y=Yes, N=No
Wrapping width . . . . . . . . . Blank, 1-378
Record on one page . . . . . . . N Y=Yes, N=No
F3=Exit F5=Report F10=Process/previous
F12=Cancel F13=Layout F18=Files
‘Output type’ is 3 to create a file. ‘Form of output’ is 1 to write all the detail records to the file, or 2 to only write the totals and subtotals (the defined report breaks).
Define Database File Output
Type choices, press Enter.
(The printed definition shows the output file record layout.)
File . . . . . . . . . testfile Name, F4 for list
Library . . . . . . QTEMP Name, F4 for list
Member . . . . . . . . *FILE Name, *FIRST, *FILE, *ALL,
F4 for list
Data in file . . . . . 2 1=New file, 2=Replace file
3=New member, 4=Replace member
5=Add to member
For a new file:
Authority . . . . . *LIBCRTAUT *LIBCRTAUT, authorization list name,
*CHANGE, *ALL, *EXCLUDE, *USE
Text . . . . . . . . test file_________________________________________
Print definition . . . N Y=Yes, N=No
F3=Exit F4=Prompt F5=Report F10=Process/previous
F12=Cancel F13=Layout F18=Files
Give the file a meaningful name and text. If you want to keep this file for production, use library UP1480BFVA or UWDASWPRDD; for your own use, put it in your work library; if it is one time only, use QTEMP (which will be removed when you log off). Be VERY careful not the name of a file that already exists; because you will replace it! Press enter.
If you want to print a report –
Select Output Type and Output Form
Type choices, press Enter.
Output type . . . . . . . . . . . 2 1=Display
2=Printer
3=Database file
Form of output . . . . . . . . . . 1 1=Detail
2=Summary only
Line wrapping . . . . . . . . . . N Y=Yes, N=No
Wrapping width . . . . . . . . . Blank, 1-378
Record on one page . . . . . . . N Y=Yes, N=No
F3=Exit F5=Report F10=Process/previous
F12=Cancel F13=Layout F18=Files
Define Printer Output
Type choices, press Enter.
Printer . . . . . . . . . *PRINT *PRINT, name
Form size:
Length . . . . . . . . . Blank, 1-255
Width . . . . . . . . . 132 Blank, 1-378
Start line . . . . . . . . Blank, 1-255
End line . . . . . . . . . Blank, 1-255
Line spacing . . . . . . . 1 1, 2, 3
Print definition . . . . . N Y=Yes, N=No
F3=Exit F5=Report F10=Process/previous
F12=Cancel F13=Layout F18=Files
The form width depends on how much data you are including (you can see by pressing F13 to show the layout). If you can go as low as 80 the report will print in portrait, with a larger font.
Notes
To get the current date –
WORK current(date)
To get last month’s date (not exactly, as months can be from 28 to 31 days; so this only works up to the 29th) –
WORK date(days(current(date))-28)
To build from and to dates to search for records dated the previous month (the ‘TO’ value won’t always be correct, but it will work) –
FROM year(work)* 10000 + month(work) * 100 + 01 TO year(work)* 10000 + month(work) * 100 + 31