This is my code. I keep getting an error that says the hours field cannot be used. I don't know why it is any different than my other nemeric fields. The field is defined as 12 with 2 dec positions.It is packed. Any suggestions?
11200 - OVRDBF FILE(ELE728PF3) SHARE(*YES)
11300 - OPNQRYF FILE((ELE728PF3)) KEYFLD((PRPPN) (DIV#) (ACT) (OBJ)
(PREMNO)) GRPFLD(PRPPN DIV PREMNO DIV# ACT OBJ) MAPFLD((HRSSUM
'%SUM(hours)') (DEBSUM '%SUM(prddra)') (CRDSUM '%sum(prdcra)') (TOTSUM
'%sum(TOTAL)'))
Field 1/HOURS not allowed in record format.
Error occurred during processing of command.
CPF9899 received by procedure ELE728CLC. (C D I R)
Additional Message Information
Message ID . . . . . . : CPD3165 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 04/27/06 Time sent . . . . . . : 09:10:32
Message . . . . : Field 1/HOURS not allowed in record format.
Cause . . . . . : When group processing is required, all fields contained in
the record format identified on the FORMAT parameter must be grouping fields
(GRPFLD parameter), or mapped fields (MAPFLD parameter) that are either
constant values or are defined using only grouping fields and aggregate
functions (%MIN or %MAX with one operand, %AVG, %COUNT, %STDDEV, %SUM, or
%VAR). Group processing is required when the GRPSLT or GRPFLD parameter is
used, or when an aggregate function is used in the definition of any field
on the MAPFLD parameter. See the CL Reference topic in the Information
Center book, http://www.ibm.com/eserver/iseries/infocenter, for more
information about the group processing on the OPNQRYF command.
Recovery . . . : You may want to specify a different record format on the
FORMAT parameter or specify additional entries on the MAPFLD parameter to
provide valid definitions for all fields in the record format identified on
the FORMAT parameter. Correct the error and try the request again.
This is the file format for the ELE728PF3:
FIELD POS POS FLD FLD DEC
NAME FROM TO TYP LNG POS
-----------------------------------
PRPPN 1 2 P 2 2, 0
DIV 3 5 A 3
PREMNO 6 11 P 6 10, 0
HOURS 12 18 P 7 12, 2
PRDTYP 19 21 A 3
FUND 22 24 A 3
DPT# 25 26 A 2
DIV# 27 29 A 3
ACT 30 32 A 3
OBJ 33 37 A 5
PRDDRA 38 43 P 6 11, 2
PRDCRA 44 49 P 6 11, 2
TOTAL 50 56 P 7 12, 2
I changed the name of the hours field and moved it further down in the record. When I reran it, it now does not like another field. The only thing I changed was hours to phrs.
11200 - OVRDBF FILE(ELE728PF3) SHARE(*YES)
11300 - OPNQRYF FILE((ELE728PF3)) KEYFLD((PRPPN) (DIV#) (ACT) (OBJ)
(PREMNO)) GRPFLD(PRPPN DIV PREMNO DIV# ACT OBJ) MAPFLD((HRSSUM
'%SUM(PHRS)') (DEBSUM '%SUM(prddra)') (CRDSUM '%sum(prdcra)') (TOTSUM
'%sum(TOTAL)'))
Field 1/PRDTYP not allowed in record format.
Error occurred during processing of command.
CPF9899 received by procedure ELE728CLC. (C D I R)
? C
Application error. CPF9899 unmonitored by ELE728CLC at statement
0000011300, instruction X'0000'.
Additional Message Information
Message ID . . . . . . : CPD3165 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 04/27/06 Time sent . . . . . . : 12:21:46
Message . . . . : Field 1/PRDTYP not allowed in record format.
Cause . . . . . : When group processing is required, all fields contained in
the record format identified on the FORMAT parameter must be grouping fields
(GRPFLD parameter), or mapped fields (MAPFLD parameter) that are either
constant values or are defined using only grouping fields and aggregate
functions (%MIN or %MAX with one operand, %AVG, %COUNT, %STDDEV, %SUM, or
%VAR). Group processing is required when the GRPSLT or GRPFLD parameter is
used, or when an aggregate function is used in the definition of any field
on the MAPFLD parameter. See the CL Reference topic in the Information
Center book, http://www.ibm.com/eserver/iseries/infocenter, for more
information about the group processing on the OPNQRYF command.
Recovery . . . : You may want to specify a different record format on the
FORMAT parameter or specify additional entries on the MAPFLD parameter to
provide valid definitions for all fields in the record format identified on
the FORMAT parameter. Correct the error and try the request again.
When you use GROUP BY in an SQL, the columns you group by are the only columns allowed to be selected. You are grouping them for a reason. Columns other than the ones you are grouping by can't really be shown because there could be different values for the same group.
Bottom line is that the only fields you can select from your OPNQRYF are those that you are grouping by. You can select counts or other aggregate functions for the grouped fields. SQL has ways to get around this using a subselect (or subquery).
What I am trying to accomplish is to create summary records with totals for some of the fields in these records so I can print a report. The fields that I am using to total and group by are the fields it is saying can't be there. So, I don't understand.
Sorry, I didn't notice that you did list the fields in the file.
Go to this link: http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/rbam6/rbam6clmain.htm
and read the section under GROUP.
I renamed field DIV to DIVISN.
I created a table identical to ELE728PF3 and called it TBL.
I then created TBLPART with the following fields:
Field
Name Type Length Dec Loc
PRPPN P 2 0 1
DIVISN A 3 3
PREMNO P 10 2 6
HRSSUM P 12 2 12
DIV# A 3 19
ACT A 3 22
OBJ A 5 25
DEBSUM P 13 2 30
CRDSUM P 13 2 37
TOTSUM P 15 2 44
Now the Group by has somewhere to put the summed values.
The other fields that are not used in this OPNQRYF statement are omitted from TBLPART.
Here is the OPNQRYF statement:
OPNQRYF FILE((TBL)) FORMAT(TIMR/TBLPART) +
KEYFLD((PRPPN) (DIV#) (ACT) (OBJ) (PREMNO)) +
GRPFLD(PRPPN DIVISN PREMNO DIV# ACT OBJ) +
MAPFLD((HRSSUM '%SUM(HRS)') +
(DEBSUM '%SUM(PRDDRA)') +
(CRDSUM '%SUM(PRDCRA)') +
(TOTSUM '%SUM(TOTAL)'))
Sorry, I didn't notice that you did list the fields in the file.
Go to this link: http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/rbam6/rbam6clmain.htm
and read the section under GROUP.
I renamed field DIV to DIVISN (I think DIV is reserved also).
I created a table identical to ELE728PF3 and called it TBL.
I then created TBLPART with the following fields:
Field
Name Type Length Dec Loc
PRPPN P 2 0 1
DIVISN A 3 3
PREMNO P 10 2 6
HRSSUM P 12 2 12
DIV# A 3 19
ACT A 3 22
OBJ A 5 25
DEBSUM P 13 2 30
CRDSUM P 13 2 37
TOTSUM P 15 2 44
Now the Group by has somewhere to put the summed values.
The other fields that are not used in this OPNQRYF statement are omitted from TBLPART.
Here is the OPNQRYF statement. Notice the FORMAT parameter:
OPNQRYF FILE((TBL)) FORMAT(TIMR/TBLPART) +
KEYFLD((PRPPN) (DIV#) (ACT) (OBJ) (PREMNO)) +
GRPFLD(PRPPN DIVISN PREMNO DIV# ACT OBJ) +
MAPFLD((HRSSUM '%SUM(HRS)') +
(DEBSUM '%SUM(PRDDRA)') +
(CRDSUM '%SUM(PRDCRA)') +
(TOTSUM '%SUM(TOTAL)'))
The FORMAT parameter describes what the query result will be like. Without it, the command assumes it's supposed to be just like the table in the FILE parameter.
11300 - OVRDBF FILE(ELE728PF3) SHARE(*YES)
11400 - OPNQRYF FILE((ELE728PF3)) FORMAT(ELE728SUM) KEYFLD((PRPPN)
(DIV#) (ACT) (OBJ) (PREMNO)) GRPFLD(PRPPN DIVISION PREMNO DIV# ACT OBJ)
MAPFLD((HRSSUM '%SUM(PHRS)') (DEBSUM '%SUM(prddra)') (CRDSUM
'%sum(prdcra)') (TOTSUM '%sum(TOTAL)')) OPNID(ELE728SUM)
Field 1/PRDDRA not allowed in record format.
Error occurred during processing of command.
CPF9899 received by procedure ELE728CLC. (C D I R)
PRDDRA is a numeric field that I am totaling, so why is it not allowed?
Layout for ele728sum:
FILE - ELE728SUM LIBRARY - BETTY
RECORD FORMAT - PR728AF TEXT - Q
---------------------------------------
FIELD POS POS FLD FLD DEC
NAME FROM TO TYP LNG POS TEXT
---------------------------------------
PRPPN 1 2 P 2 2, 0
DIVISION 3 5 A 3
PREMNO 6 11 P 6 10, 0
DIV# 12 14 A 3
ACT 15 17 A 3
OBJ 18 22 A 5
PRDDRA 23 28 P 6 11, 2
PRDCRA 29 34 P 6 11, 2
PHRS 35 41 P 7 12, 2
TOTAL 42 48 P 7 12, 2
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 10  Replies