th3louvre - 1 year ago 36

SQL Question

I have a table,

`report_total`

`total_types_cd`

If there is no corresponding row, I'd like to have a placeholder in the select such that the

`renamed total_amt`

`unitem_cntrib`

`total_contrib`

`0`

`COALESCE`

These query results are going into a pdf report and so I want something, even if it is 0. Right now, no report is getting generated because the select returns no row if

`$P{ReportID}`

`SELECT unitem_cntrib, total_cntrib, unitem_expnd, total_expnd,`

unitem_pldg, on_hand, tot_loan, unitem_loan

FROM

(select total_amt from report_total where calculation_type_cd ='UNITEMIZED_PLUS_LUMPSUM'

and total_type_cd = 'TOT_CNTRB' and report_info_id=$P{ReportID} ) AS unitem_cntrib,

(select total_amt from report_total where calculation_type_cd ='GRANDTOTAL'

and total_type_cd = 'TOT_CNTRB' and report_info_id=$P{ReportID} ) AS total_cntrib,

(select total_amt from report_total where calculation_type_cd ='UNITEMIZED_PLUS_LUMPSUM'

and total_type_cd = 'TOT_EXPND' and report_info_id=$P{ReportID} ) AS unitem_expnd,

(select total_amt from report_total where calculation_type_cd ='GRANDTOTAL'

and total_type_cd = 'TOT_EXPND' and report_info_id=$P{ReportID} ) AS total_expnd,

(select total_amt from report_total where calculation_type_cd ='UNITEMIZED_PLUS_LUMPSUM'

and total_type_cd = 'TOT_PLEDGE' and report_info_id=$P{ReportID} ) AS unitem_pldg,

(select total_amt from report_total where calculation_type_cd ='LUMPSUM'

and total_type_cd = 'TOT_CNTRB_BALANCE' and report_info_id=$P{ReportID} ) AS on_hand,

(select total_amt from report_total where calculation_type_cd ='LUMPSUM'

and total_type_cd = 'TOT_LOAN_PRINCIPAL' and report_info_id=$P{ReportID} ) AS tot_loan,

(select total_amt from report_total where calculation_type_cd ='UNITEMIZED_PLUS_LUMPSUM'

and total_type_cd = 'TOT_LOAN' and report_info_id=$P{ReportID} ) AS unitem_loan

Answer Source

I think you want conditional aggregation:

```
select max(case when calculation_type_cd = 'UNITEMIZED_PLUS_LUMPSUM' and total_type_cd = 'TOT_CNTRB'
then total_amt end) as unitem_cntrib,
max(case when calculation_type_cd = 'GRANDTOTAL' and total_type_cd = 'TOT_CNTRB'
then total_amt end) as total_cntrib,
. . .
from report_total rt
where rt.report_info_id = $P{ReportID};
```