opnqryf problem with grouping

40 pts.
Tags:
Application development
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)

Answer Wiki

Thanks. We'll let you know when a new response is added.

Field 1/HOURS not allowed in record format.

Do an F1 on this line to get more information. Let us know what it is even if you figure it out yourself.

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Qbjgqbjg
    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
    40 pointsBadges:
    report
  • Geneburns
    HOURS is a reserved duration keyword. So it probably does not recognize this as a field name.
    15 pointsBadges:
    report
  • Qbjgqbjg
    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.
    40 pointsBadges:
    report
  • TimRac
    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).
    25 pointsBadges:
    report
  • Qbjgqbjg
    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.
    40 pointsBadges:
    report
  • TimRac
    Put the file layout of ELE728PF3 so we can see what it's selecting from.
    25 pointsBadges:
    report
  • TimRaz
    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)'))
    0 pointsBadges:
    report
  • TimRaz
    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.
    0 pointsBadges:
    report
  • Qbjgqbjg
    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
    40 pointsBadges:
    report
  • Qbjgqbjg
    Ignore last response. I have the names wrong, they should be the result names. Sorry about that.
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following