Information Systems:Query/400 - Revision history

Norwinu at 17:32, 16 August 2017

2017-08-16T17:32:46Z

Norwinu at 16:23, 23 June 2016

2016-06-23T16:23:49Z

Norwinu at 07:24, 23 June 2016

2016-06-23T07:24:38Z

Norwinu: Norwinu moved page Information Systems:QUERY to Information Systems:Query/400

2016-06-23T07:14:20Z

Norwinu moved page Information Systems:QUERY to Information Systems:Query/400

Norwinu at 07:13, 23 June 2016

2016-06-23T07:13:46Z

New page

=Query=

‘Query’ is a tool to make an SQL call easier to define, and to use. [[SQL|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

[[Category: Information Systems]]
[[Category: Database Query]]

Sheilav at 21:44, 30 October 2015

2015-10-30T21:44:44Z

Sheilav: Created page with "‘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 managin..."

2015-10-30T21:43:55Z

Created page with "‘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 managin..."

New page

‘Query’ is a tool to make an SQL call easier to define, and to use. [[SQL|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

← Older revision		Revision as of 17:32, 16 August 2017
Line 1:		Line 1:
		+	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. -[[User:Norwinu\|norwizzle]] ([[User talk:Norwinu\|talk]])
		+
	=Query=		=Query=

← Older revision		Revision as of 16:23, 23 June 2016
Line 404:		Line 404:
	TO year(work)* 10000 + month(work) * 100 + 31		TO year(work)* 10000 + month(work) * 100 + 31

		+	[[Category: IBM i Tools/Utilities]]
	[[Category: Database Query]]		[[Category: Database Query]]

← Older revision		Revision as of 21:44, 30 October 2015
Line 1:		Line 1:
		+	=Query=
		+
	‘Query’ is a tool to make an SQL call easier to define, and to use. [[SQL\|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.		‘Query’ is a tool to make an SQL call easier to define, and to use. [[SQL\|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.

← Older revision		Revision as of 07:24, 23 June 2016
Line 404:		Line 404:
	TO year(work)* 10000 + month(work) * 100 + 31		TO year(work)* 10000 + month(work) * 100 + 31

−	[[Category: Information Systems]]
	[[Category: Database Query]]		[[Category: Database Query]]