I have a query ...
SELECT
um00200M.PERSON_LNM
,um00163t.CONS_AMT
,um00163t.BILLED_DTM
,um00163t.READ_DTM
,UM00261T.ACCOUNT_NO
,UM00261T.BILL_RUN_DT
,UM00261T.BILL_RUN_TM
,CASE WHEN UM00262T.BC_DETAIL_TP = 1 THEN UM00262T.BC_DETAIL_AMT END AS CHARGE_AMT
,CASE WHEN UM00262T.BC_DETAIL_TP = 3 THEN UM00262T.BC_DETAIL_AMT END AS BASE_AMT
,UM00262T.BC_DETAIL_SEQ
FROM UM00261T
INNER JOIN UM00262T
ON UM00261T.ACCOUNT_NO = UM00262T.ACCOUNT_NO
AND UM00261T.BILL_RUN_DT = UM00262T.BILL_RUN_DT
AND UM00261T.BILL_RUN_TM = UM00262T.BILL_RUN_TM
LEFT OUTER JOIN
um00200M
on um00200M.ACCOUNT_NO = um00261t.ACCOUNT_NO
INNER JOIN
um00163t
ON um00163t.ACCOUNT_NO= um00262t.ACCOUNT_NO
WHERE 1=1
AND UM00262T.BC_DETAIL_TP = 1
--AND UM00262T.BC_DETAIL_SEQ = 2
OR UM00262T.BC_DETAIL_TP = 3
--AND UM00262T.BC_DETAIL_SEQ = 1
AND UM00163T.ACCOUNT_NO = UM00261T.ACCOUNT_NO
This produces rwo records a charge amount and a base amount. I would like to combine the records into one record. How would I do this ?
have you tried using repeater for the column?
Quote from: jaymoore1756 on 22 Nov 2015 09:14:01 AM
I have a query ...
SELECT
um00200M.PERSON_LNM
,um00163t.CONS_AMT
,um00163t.BILLED_DTM
,um00163t.READ_DTM
,UM00261T.ACCOUNT_NO
,UM00261T.BILL_RUN_DT
,UM00261T.BILL_RUN_TM
,CASE WHEN UM00262T.BC_DETAIL_TP = 1 THEN UM00262T.BC_DETAIL_AMT END AS CHARGE_AMT
,CASE WHEN UM00262T.BC_DETAIL_TP = 3 THEN UM00262T.BC_DETAIL_AMT END AS BASE_AMT
,UM00262T.BC_DETAIL_SEQ
FROM UM00261T
INNER JOIN UM00262T
ON UM00261T.ACCOUNT_NO = UM00262T.ACCOUNT_NO
AND UM00261T.BILL_RUN_DT = UM00262T.BILL_RUN_DT
AND UM00261T.BILL_RUN_TM = UM00262T.BILL_RUN_TM
LEFT OUTER JOIN
um00200M
on um00200M.ACCOUNT_NO = um00261t.ACCOUNT_NO
INNER JOIN
um00163t
ON um00163t.ACCOUNT_NO= um00262t.ACCOUNT_NO
WHERE 1=1
AND UM00262T.BC_DETAIL_TP = 1
--AND UM00262T.BC_DETAIL_SEQ = 2
OR UM00262T.BC_DETAIL_TP = 3
--AND UM00262T.BC_DETAIL_SEQ = 1
AND UM00163T.ACCOUNT_NO = UM00261T.ACCOUNT_NO
This produces rwo records a charge amount and a base amount. I would like to combine the records into one record. How would I do this ?
Is this manually coded SQL in your report? The recommended approach would be to model in Framework Manager. Anything else will be limiting in terms of ongoing use and maintenance.
Having said that.....
Since you are using an outer join it is possible that you will get null values for the amounts which won't play nicely with real figures. I'd suggest you integrate a coalesce statement so that if there is no value it will return zero. Then you can aggregate properly by putting a sum around your metrics and doing a group by around all the other columns.