Clever Geek Handbook

Is there a way to have a placeholder if select doesn't return rows?

I have a table report_total

that contains computed values ​​for some of total_types_cd

(codes), but not necessarily all of them.

If there is no matching row, I would like to have a placeholder in the select, so that renamed total_amt

( unitem_cntrib

/ total_contrib

...) matters 0

, and I always get 8 items returned, even if no values ​​were found. I thought maybe the function COALESCE

might work, but I was unable to write a request that was acceptable.

These query results end up in a pdf report, so I want something even if it is 0. At the moment, no report is generated because select does not return a row if all is not present. Below is my select statement and $P{ReportID}

is injected 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
+3


source to share


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


source






More articles:


All Articles