469,290 Members | 1,913 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,290 developers. It's quick & easy.

GROUPING IS INCONSISTENT error message

2
Hi all,

I am receiving a 'GROUPING IS INCONSISTENT' error message on the following SQL code. Any ideas ?

================================================== =======

SELECT DISTINCT C.C_ACCT_CA_PRFX||
SUBSTR(DIGITS(A.I_ACCT_CA),3,8)||
SUBSTR(DIGITS(A.I_TNT),4,2) ACCOUNT_NUMBER,
B.A_PK_CIS_CR_BAL,
SUM(B.A_KCM_TRAN_AMT),
SUM(D.A_BC_ORIG),
SUM(D.A_VAT_CHRG_ORIG),
SUM(D.A_VAT_PRPD_CHG_OUT)

FROM PRGP.TS083_CUSTOMER A,
PRGP.TR045_BLFIN_COCON C,
PRGP.TS322_POW_KEY_ACT B,
PRGP.TS032_BILL_CHRG D

WHERE A.C_CO = C.C_CO
AND B.I_ACCT_CA = D.I_ACCT_CA
AND B.I_TNT = D.I_TNT
AND B.I_ACCT_CA = A.I_ACCT_CA
AND B.I_TNT = A.I_TNT
AND A.I_ACCT_CA = 00101141
AND A.I_TNT = 03
GROUP BY B.A_KCM_TRAN_AMT,
D.A_BC_ORIG,
D.A_VAT_CHRG_ORIG,
D.A_VAT_PRPD_CHG_OUT
May 17 '07 #1
3 11377
frozenmist
179 Expert 100+
Hi EVANSY,
As I see it, you need not group the columns used in the sum().
In that case how will you get the sum? But this is not an error, i think.

I think the problem is that you need to group all other columns that have been mentioned in the select not using aggregate function like sum.
eg: you haven't added the columns which are part of account_number in group by.

Try out these.

Hope you find the solution
Cheers
May 17 '07 #2
EVANSY
2
Thanks for the advice but still appear to be getting the problem using the new code below.

Any ideas ?

================================================== =======

SELECT DISTINCT C.C_ACCT_CA_PRFX||
SUBSTR(DIGITS(A.I_ACCT_CA),3,8)||
SUBSTR(DIGITS(A.I_TNT),4,2) ACCOUNT_NUMBER,
B.A_PK_CIS_CR_BAL TOTAL_BALANCE,
SUM(B.A_KCM_TRAN_AMT) TOTAL_KEYCHARGES,
SUM(D.A_BC_ORIG) TOTAL_CHARGES,
SUM(D.A_VAT_CHRG_ORIG) ORIGINAL_VAT,
SUM(D.A_VAT_PRPD_CHG_OUT) TOTAL_VAT

FROM PRGP.TS083_CUSTOMER A,
PRGP.TR045_BLFIN_COCON C,
PRGP.TS322_POW_KEY_ACT B,
PRGP.TS032_BILL_CHRG D

WHERE A.C_CO = C.C_CO
AND B.I_ACCT_CA = D.I_ACCT_CA
AND B.I_TNT = D.I_TNT
AND B.I_ACCT_CA = A.I_ACCT_CA
AND B.I_TNT = A.I_TNT
AND A.I_ACCT_CA = 00101141
AND A.I_TNT = 03
GROUP BY A.I_ACCT_CA,
A.I_TNT,
B.A_PK_CIS_CR_BAL

================================================== =======
May 18 '07 #3
frozenmist
179 Expert 100+
Hi Evansy,
You also have to have
C.C_ACCT_CA_PRFX
in the group by clause.

Try this also
Cheers
May 18 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by PC Datasheet | last post: by
reply views Thread by jlea | last post: by
1 post views Thread by Peter Steele | last post: by
5 posts views Thread by Andy Fish | last post: by
reply views Thread by Corey | last post: by
1 post views Thread by Sandeep Singh | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.