IBC233 Practice Test 2
Query practice ... Consider the following Physical Files which can be found in the IBC233LIB course library.
* PRODPF = Product Master physical file
UNIQUE
R PRODPFR
NUMBER 4A TEXT('PRODUCT NUMBER')
DESC 30A TEXT('PRODUCT DESCRIPTION')
COLOUR 10A TEXT('PRODUCT COLOUR')
PRICE 7P 2 TEXT('PRODUCT UNIT PRICE')
K NUMBER
* INVPF = Inventory Master physical file
UNIQUE
R INVPFR
NUMBER 4A TEXT('PRODUCT NUMBER')
LOCATION 5A TEXT('STOCKING LOCATION')
QOH 7P 0 TEXT('QUANTITY ON HAND')
QOO 7P 0 TEXT('QUANTITY ON ORDER')
K NUMBER
RUNQRY *N PRODPF produces the following output:
NUMBER DESC COLOUR PRICE
D101 Driver Side Front Door RED 150.00
D102 Passenger Side Front Door BLUE 150.00
D104 Passenger Side Back Door RED 125.00
D103 Driver Side Back Door RED 125.00
TA4X 4 speed automatic transmission 1,000.00
TM5F 5 speed manual transmission 900.00
E200 6 cylinder engine 1,050.00
E100 4 cylinder engine 800.00
E300 8 cylinder engine 1,350.00
D105 Rear Hatch Door GREEN 542.00
RUNQRY *N INVPF produces the following output:
NUMBER LOCATION QOH QOO
D101 A5S34 2 0
D104 A2S06 0 10
D103 A1S02 5 1
D105 A7S15 1 13
E100 R4L2 7 0
E300 R2L1 0 2
TM5F R8L1 2 1
Given the query output on the following pages, can you recreate those queries?
In the test, you will be asked to describe in detail what you would do to produce such a query.
Your answer does not need to be a keystroke by keystroke account but more like you were telling a user over the phone exactly what to do (the user at the other end of the phone does not have any of the documentation you see before you).
You can practice using the files in IBC233LIB.
2018/11/16Page 1 of 10
IBC233 Practice Test 2
Query 1
Product Description COLOUR LOCATION Quantity
Number On Hand
D101 Driver Side Front Door RED A5S34 2
D103 Driver Side Back Door RED A1S02 5
D104 Passenger Side Back Door RED A2S06 0
D105 Rear Hatch Door GREEN A7S15 1
E100 4 cylinder engine R4L2 7
E300 8 cylinder engine R2L1 0
TM5F 5 speed manual transmission R8L1 2
Solution
Opt Query Definition Option
> 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
Specify File Selections
Type choices, press Enter. Press F9 to specify an additional
file selection.
File ...... PRODPF Name, F4 for list
Library ...... IBC233LIB Name, *LIBL, F4 for list
Member ...... *FIRST Name, *FIRST, F4 for list
Format ...... PRODPFR Name, *FIRST, F4 for list
File ID ...... P A-Z99, *ID
File ...... INVPF Name, F4 for list
Library ...... IBC233LIB Name, *LIBL, F4 for list
Member ...... *FIRST Name, *FIRST, F4 for list
Format ...... INVPFR Name, *FIRST, F4 for list
File ID ...... I A-Z99, *ID
Specify Type of Join
Type choice, press Enter.
Type of join ...... 1 1=Matched records
2=Matched records with primary file
3=Unmatched records with primary file
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
P.NUMBER EQ I.NUMBER
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
10 P.NUMBER
20 P.DESC
30 P.COLOUR
40 I.LOCATION
50 I.QOH
P.PRICE
I.NUMBER
I.QOO
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
10 A P.NUMBER
P.DESC
P.COLOUR
I.LOCATION
I.QOH
Specify Report Column Formatting
Type information, press Enter.
Column headings: *NONE, aligned text lines
Column
Field Spacing Column Heading Len Dec Edit
P.NUMBER 0 Product 4
Number
P.DESC 2 Description 30
P.COLOUR 2 COLOUR 10
I.LOCATION 2 LOCATION 5
I.QOH 2 Quantity 7 0
On Hand
Query 2
Product Description COLOUR Quantity LOCATION
Number On Order
D101 Driver Side Front Door RED 0 A5S34
D102 Passenger Side Front Door BLUE 0
D104 Passenger Side Back Door RED 10 A2S06
D103 Driver Side Back Door RED 1 A1S02
TA4X 4 speed automatic transmission 0
TM5F 5 speed manual transmission 1 R8L1
E200 6 cylinder engine 0
E100 4 cylinder engine 0 R4L2
E300 8 cylinder engine 2 R2L1
D105 Rear Hatch Door GREEN 13 A7S15
Query 3
COLOUR Product Description PRICE
Number
BLUE D102 Passenger Side Front Door 150.00
COUNT 1
GREEN D105 Rear Hatch Door 542.00
COUNT 1
RED D101 Driver Side Front Door 150.00
RED D103 Driver Side Back Door 125.00
RED D104 Passenger Side Back Door 125.00
COUNT 3
FINAL TOTALS
COUNT 5
Query 4
NUMBER DESC COLOUR PRICE LOCATION QOH QOO
D102 Passenger Side Front Door BLUE 150.00 0 0
E200 6 cylinder engine 1,050.00 0 0
TA4X 4 speed automatic transmission 1,000.00 0 0
Query 5
Product Description Quantity Unit Extended
Number On Hand Price Price
D103 Driver Side Back Door 5 125.00 $625
D101 Driver Side Front Door 2 150.00 $300
D105 Rear Hatch Door 1 542.00 $542
E100 4 cylinder engine 7 800.00 $5,600
TM5F 5 speed manual transmission 2 900.00 $1,800
FINAL TOTALS
TOTAL $8,867
Your Answer
Specify file selections / Specify report column formattingDefine result fields / Select report summary functions
Select and sequence fields / Define report breaks
Select records / Select output type and output form
Select sort fields
Solution
Details of Query Definition expected on a test. e.g. for Query 5
Specify File SelectionsPrimary: PRODPF, ID: P Secondary: INVPF, ID: I
Type of join: 1=Matched records where
P.NUMBER EQ I.NUMBER
Define Result Fields
EXTPRICE i.qoh * p.price
Select and Sequence Fields
P.NUMBER, P.DESC, I.QOH, P.PRICE , EXTPRICE
Select Records
I.QOH NE 0
Select Sort Fields
P.DESC
Specify Report Column Formatting
all Column Headings specified in words and Upper/Lower case
EXTPRICE edited with code J, and Floating currency symbol
Select Report Summary Functions
Total of EXTPRICE
Define Report Breaks
[ none for Query 5 but Query 3 has a break on COLOUR ]
Select Output Type and Form
[ Query 5 does not specify type of output. If it said "create a spooled file", then…
Output type: Printer ]
2018/11/16Page 1 of 10