query calcualtion in a list report

[zviko]

hey guys, am getting stuck here, wanna produce a report with simple addition of values in columns. this is the query generated by cognos report studio

with "TXN_DATE_DIMENSION11" as (
select "TXN_DATE_DIMENSION"."DAYKEY" "DAYKEY" , "TXN_DATE_DIMENSION"."DAYDATE" "DAYDATE"
from "SHOP_EASY"."TXN_DATE_DIMENSION" "TXN_DATE_DIMENSION"
where "TXN_DATE_DIMENSION"."DAYDATE" < current_date), "D8" as (
select "T0"."C0" "Customer" , "T0"."C1" "Customer_Number" , "T0"."C2" "Account_Code" , "T0"."C3" "Receipts" , "T0"."C4" "Payments" , "T0"."C3" "rc" , "T0"."C4" "rc7"
from (
select "CUSTOMER_DIM10"."CUSTOMER_NAME" "C0" , "CUSTOMER_DIM10"."CST_NUMBER" "C1" , "CUSTOMER_DIM10"."CST_ACCOUNT_CODE" "C2" , sum(case  when "TRANSACTION_FACT"."TXN_CODE" in ('PMT', 'STAP', 'EPAY', 'DEP') then "TRANSACTION_FACT"."TRANSACTION_VALUE" else 0 end ) "C3" , sum(case  when "TRANSACTION_FACT"."TXN_CODE" in ('INV', 'EFT') then "TRANSACTION_FACT"."TRANSACTION_VALUE" else 0 end ) "C4"
from "SHOP_EASY"."CUSTOMER_DIM" "CUSTOMER_DIM10", "SHOP_EASY"."TRANSACTION_FACT" "TRANSACTION_FACT", "TXN_DATE_DIMENSION11"
where "TRANSACTION_FACT"."TXN_CODE" in ('ADV', 'BDRE', 'BDRR', 'BPMT', 'BRTV', 'BWOR', 'COUP', 'CRNR', 'DEPR', 'EFT', 'EPAY', 'ESB', 'ESBR', 'HOVR', 'HPRE', 'INTR', 'LRCR', 'LWOR', 'PMT', 'PMTR', 'RBJC', 'RBJD', 'RDPM', 'SBJD', 'SDPR', 'STAP', 'STPA', 'UNRR') and "CUSTOMER_DIM10"."CST_KEY" = "TRANSACTION_FACT"."CST_KEY" and "TXN_DATE_DIMENSION11"."DAYKEY" = "TRANSACTION_FACT"."DAYKEY"
group by "CUSTOMER_DIM10"."CUSTOMER_NAME", "CUSTOMER_DIM10"."CST_NUMBER", "CUSTOMER_DIM10"."CST_ACCOUNT_CODE") "T0"), "TXN_DATE_DIMENSION13" as (
select "TXN_DATE_DIMENSION"."DAYKEY" "DAYKEY" , "TXN_DATE_DIMENSION"."DAYDATE" "DAYDATE"
from "SHOP_EASY"."TXN_DATE_DIMENSION" "TXN_DATE_DIMENSION"
where "TXN_DATE_DIMENSION"."DAYDATE" < current_date), "D9" as (
select "T0"."C0" "Customer" , "T0"."C1" "Customer_Number" , "T0"."C2" "Account_Code" , "T0"."C3" "Opening_Balance" , "T0"."C4" "Closing_Balance" , "T0"."C3" "rc"
from (
select "CUSTOMER_DIM12"."CUSTOMER_NAME" "C0" , "CUSTOMER_DIM12"."CST_NUMBER" "C1" , "CUSTOMER_DIM12"."CST_ACCOUNT_CODE" "C2" , sum(case  when "TXN_DATE_DIMENSION13"."DAYDATE" = ("TXN_DATE_DIMENSION13"."DAYDATE" - DAY("TXN_DATE_DIMENSION13"."DAYDATE") DAY + 1 DAY) then "CUSTOMER_BALANCE"."BALANCE" else NULL end ) "C3" , sum(case  when "TXN_DATE_DIMENSION13"."DAYDATE" = (("TXN_DATE_DIMENSION13"."DAYDATE" - DAY("TXN_DATE_DIMENSION13"."DAYDATE") DAY + 1 DAY) + 1 MONTH - 1 DAY) then "CUSTOMER_BALANCE"."BALANCE" else NULL end ) "C4"
from "SHOP_EASY"."CUSTOMER_DIM" "CUSTOMER_DIM12", "TXN_DATE_DIMENSION13", "SHOP_EASY"."CUST_BALANCE" "CUSTOMER_BALANCE"
where "CUSTOMER_DIM12"."CST_ACCOUNT_CODE" = "CUSTOMER_BALANCE"."CUST_ACCOUNT_CODE" and "TXN_DATE_DIMENSION13"."DAYKEY" = "CUSTOMER_BALANCE"."DAYKEY"
group by "CUSTOMER_DIM12"."CUSTOMER_NAME", "CUSTOMER_DIM12"."CST_NUMBER", "CUSTOMER_DIM12"."CST_ACCOUNT_CODE") "T0")
select (coalesce("D8"."Customer", "D9"."Customer")) "Customer" , (coalesce("D8"."Customer_Number", "D9"."Customer_Number")) "Customer_Number" , (coalesce("D8"."Account_Code", "D9"."Account_Code")) "Account_Code" , "D9"."Opening_Balance" "Opening_Balance" , "D9"."Closing_Balance" "Closing_Balance" , "D8"."Receipts" "Receipts" , "D8"."Payments" "Payments" , "D9"."rc" + "D8"."rc" "Opening_Balance___Receipts" , "D9"."rc" + "D8"."rc7" "Closing_Balance____Payments"
from "D8" full outer join "D9" on "D8"."Customer" = "D9"."Customer" and "D8"."Customer_Number" = "D9"."Customer_Number" and "D8"."Account_Code" = "D9"."Account_Code" FOR FETCH ONLY

but the results are not correct as shown in the attached screenshot. NB where there are gaps the value is null.

please help! :(

[Lynn]

Anything + null = null

Try using a coalesce for your metrics to make them zero instead of null