Summing on different columns in same table 2

[thrybergh]

Table ACC.RGC_BALANCES has the following structure:

ACTCDE - Account code
ACCGRP - Account group
BAL_RC - Balance for RC type projects
BAL_EC - Balance for EC type projects


The following SQL script populates the ACTCDE, ACCGRP and BAL_RC table correctly:

INSERT INTO ACC.RGC_BALANCES
(ACTCDE, ACCGRP, BAL_RC)
SELECT 
	A.ACTCDE, 
	A.ACCGRP, 
	SUM(B.PROJECT_TO_DATE_NET)
FROM 
	ACC.RGC_ACCOUNTS A, 
	ACC.GL_PARENT_CHILD_VALUES P, 
	ACC.GL_BALANCES_AF B
WHERE 
	AND P.CHILD_FLEX_VALUE = B.ACCOUNT_RC
	AND P.PARENT_FLEX_VALUE = A.ACCGRP
	AND A.ACTCDE = B.ACCT$ACTIVITY
GROUP BY 
	A.ACTCDE,
	A.ACCGRP

The script above sums the balances by B.ACCOUNT_RC. This is a category code.
Next, I need to populate the BAL_EC column in ACC.RGC_BALANCES by using
the same method, but substituting B.ACCOUNT_RC for B.ACCOUNT_EC which is
a different category code.

Do I need to re-run the above SQL script, but altering it to UPDATE the BAL_EC column
in the table, or is there a clever way to join the ACC.GL_BALANCES_AF table to itself
and insert the two sum columns in one pass?

If anyone has any advice, I'd be grateful!

Thanks.

 

[R1972]

TRY THE FOLLOWING STATEMENT.


INSERT INTO ACC.RGC_BALANCES
(ACTCDE, ACCGRP, BAL_RC, BAL_EC)
SELECT
A.ACTCDE,
A.ACCGRP,
DECODE(P.CHILD_FLEX_VALUE, B.ACCOUNT_RC,
SUM(B.PROJECT_TO_DATE_NET), 0) BAL_RC,
DECODE(P.CHILD_FLEX_VALUE, B.ACCOUNT_EC,
SUM(B.PROJECT_TO_DATE_NET), 0) BAL_EC,

FROM
ACC.RGC_ACCOUNTS A,
ACC.GL_PARENT_CHILD_VALUES P,
ACC.GL_BALANCES_AF B
WHERE
AND P.CHILD_FLEX_VALUE IN(B.ACCOUNT_RC, B.ACCOUNT_EC)
AND P.PARENT_FLEX_VALUE = A.ACCGRP
AND A.ACTCDE = B.ACCT$ACTIVITY
GROUP BY
A.ACTCDE,
A.ACCGRP

 

[thrybergh]

Hello.

Thanks for the idea. It complained at first, with the "NOT A GROUP BY EXPRESSION" error. I've moved the SUM to before the DECODE and am trying again.

We have a performance issue here, as the tables and views (Noetix) are complex and there are hundreds of thousands of rows in our ledger tables. This makes testing more difficult and it is the reason we extract the data into tables in the first place.

Cheers.

 

[thrybergh]

Hello R1972.

I've got to hand it to you- that reply of yours worked a treat (after a little change)!

SELECT 
    A.ACTCDE, 
    A.ACCGRP, 
    SUM(DECODE(P.CHILD_FLEX_VALUE, B.ACCOUNT_RC,
             (B.PROJECT_TO_DATE_NET), 0)) BAL_RC,
    SUM(DECODE(P.CHILD_FLEX_VALUE, B.ACCOUNT_EC,
             (B.PROJECT_TO_DATE_NET), 0)) BAL_EC

Thank you very much. You're a star.