Posted: Thu May 29, 2008 12:41 pm Post subject: SQLCODE = -122
Hi,
I am trying below query but getting SQLCODE = -122.
Code:
SELECT RXXXD_XXR,
XXXX_BXXXX_AXXX_XD,
COUNT(RXXXXD_ATY)
FROM MEARSK1.MENST_FOR_BEERS
WHERE BXXXC_ZRP_AD = '123ABC'
GROUP BY XXXX_BXXXX_AXXX_XD, RXXXXD_ATY
;
I am trying to acheive the below:
Quote:
Provide a list of allocation quantities grouped by XXXX_BXXXX_AXXX_XD for 123ABC
Please help.
Thanks. _________________ Ranga
*****
None of us is as smart as all of us - Ken Blanchard
You have to include every expression of the SELECT clause into the GROUP BY clause, that is not a function. With some expressions (e.g. CASE statements, concatenations, calculations) this is not possible. Take this as a "preview on comming attractions" if you are dealing with the GROUP BY clause.
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Thu Jun 12, 2008 9:57 am Post subject:
CZerfas wrote:
You have to include every expression of the SELECT clause into the GROUP BY clause, that is not a function. With some expressions (e.g. CASE statements, concatenations, calculations) this is not possible. Take this as a "preview on comming attractions" if you are dealing with the GROUP BY clause.
regards
Christian
If I'm reading this correctly your saying you CANNOT have a CASE statment with a Group BY. Can you please clarify? I'm currently running queries that include Group By and Case statments (Case statments are included in the Group BY) . _________________ Thanks,
NASCAR9
this list of expressions not suitable for the GROUP BY clause ist getting smaller with every new DB2 release. That you can use CASE constructs in the GROUP BY clause ist very good. It's a pity, though, that you cannot use ALL expressions in the GROUP BY clause or at least a positional parameter instead. That was the message of my previous post.
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Mon Jun 16, 2008 12:53 pm Post subject:
Christian,
I'm still a little confused by your statments. So here is an example I created with CASE statements, concatenations, calculations and ran in SPUFI. We are running V8 DB2 on z/OS.
Code:
SELECT
C.CLAIM_ID
,SUBSTR(DIGITS(C.CLAIM_ID),01,4)
|| '.' ||
SUBSTR(DIGITS(C.CLAIM_ID),08,2) AS CLAIM_CON
,COALESCE(SUBSTR(CHAR(C.OCC_TO_DATE ),9,2),
SUBSTR(CHAR(C.OCC_FROM_DATE),9,2)) AS CLAIM_DAY
,CASE WHEN C.MED_PLAN IS NULL THEN ' '
WHEN SUBSTR(C.MED_PLAN,1,1) = ' ' THEN ' '
WHEN SUBSTR(C.MED_PLAN,1,1) = 'R' THEN 'RR'
WHEN SUBSTR(C.MED_PLAN,1,1) = 'A' THEN 'AA'
ELSE 'XX'
END AS CASE_TEST
,SUM (COALESCE(C.ALLOWED_AMT, 0)) AS TOT_ALLOWED
,SUM (COALESCE(C.OTH_INS_AMT, 0)) AS TOT_OTHER_INS
,SUM (COALESCE(C.PAID_AMT , 0)) AS TOT_PAID
,SUM (COALESCE(C.ALLOWED_AMT, 0) -
COALESCE(C.OTH_INS_AMT, 0) -
COALESCE(C.PAID_AMT, 0)
) AS CALC_TEST
FROM FDBMD.MEDDTL C
WHERE C.CLAIM_ID = 0007433467
GROUP BY
C.CLAIM_ID
,SUBSTR(DIGITS(C.CLAIM_ID),01,4)
|| '.' ||
SUBSTR(DIGITS(C.CLAIM_ID),08,2)
,COALESCE(SUBSTR(CHAR(C.OCC_TO_DATE ),9,2),
SUBSTR(CHAR(C.OCC_FROM_DATE),9,2))
,CASE WHEN C.MED_PLAN IS NULL THEN ' '
WHEN SUBSTR(C.MED_PLAN,1,1) = ' ' THEN ' '
WHEN SUBSTR(C.MED_PLAN,1,1) = 'R' THEN 'RR'
WHEN SUBSTR(C.MED_PLAN,1,1) = 'A' THEN 'AA'
ELSE 'XX'
END;
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 35
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 51
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
