Is there a way to have a placeholder when select returns no rows?

I have a table, report_total, that contains calculated values for some of the total_types_cd (codes) but not necessarily all of them.

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…) has a value of 0 and I always get a return of 8 items even if no values were found. I thought maybe the COALESCE function might work but I have not been able to write a query that was acceptable.

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 all the values are not present. Below is my select statement and the $P{ReportID} is input into the report generator.

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

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};

Advertisement

Answer