All Forums UDA
marcmc 112 posts Joined 12/05
09 Jul 2007
Case Statement 2

I've walked myself into a wall tonight. The below works but when I attempt to add in the 2 commented out lines i get syntax error. the desired result is to ensure that when the 1st(biggest) case statement sets (coalesce(r9.rated_driver_code,0)) then it will also check to see if c.tr_single_driver is null and if so set it to be '-'. Am I missing something blatently obvious?SELECT top 5 (CASE WHEN r9.rated_driver_id Is Not Null AND t9.rated_P_driver_code Is Null AND 'H' || trim(t7.corrected_product_type) in ('HQMV', 'HQMF', 'HQMP', 'HQMB') THEN (CASE WHEN c.trans_seq_no IS NULL AND c.no_qmrneg_det = 'N' THEN NULL ELSE (CASE WHEN r9.rated_driver_id IS NOT NULL THEN r9.rated_driver_id WHEN c.Product_Type = 'QMP' THEN r.rated_driver_id ELSE NULL END) END)WHEN t9.rated_P_driver_code IS NULL THEN (coalesce(r9.rated_driver_code,0)) -- (case when c.tr_single_driver IS NULL then (coalesce(c.tr_single_driver,'-')) -- else c.tr_single_driver end) tr_single_driver ELSE NULL END) rated_p_driver_id, r9.rated_driver_id, t9.rated_P_driver_code, 'H' || trim(t7.corrected_product_type), t2.Policy_id FROM po_risk_detail_cursor c LEFT JOIN tmp_po_rsk_dtl_sales_rep t ON c.cursor_id = t.cursor_id.....

Barry-1604 176 posts Joined 07/05
09 Jul 2007

If your intent is to calculate a column called "tr_single_driver" without actually selecting the column, then you can do something like this:WHEN t9.rated_P_driver_code IS NULLTHEN (coalesce(r9.rated_driver_code,0)) + POSITION('ZZZ' IN ((case when c.tr_single_driver IS NULL then (coalesce(c.tr_single_driver,'-')) else c.tr_single_driver end) (named tr_single_driver)))ELSE NULL ENDThe 'ZZZ' should be replaced with something that could not possibly occur in the column, so that the POSITION function will always yield a result of zero. That way, it won't affect the calculation of "rated_p_driver_id". So, you'll still get to reference the "tr_single_driver" column elsewhere in the query without the need to select it as a separate column. I would probably name it something other than the original column name or you may have problems getting it to reference the right variable whenever you reference "tr_single_driver".There are various other methods to doing this as well...this is just the 1st one that came to mind.Good luck!

marcmc 112 posts Joined 12/05
10 Jul 2007

Thanks barry but it does need to reference the column. (po_risk_detail_cursor.tr_single_driver)I'm not trying to use any variables.the logic simply is If r9.rated_driver_id Is Not Null AND t9.rated_P_driver_code Is Null AND 'H' || trim(t7.corrected_product_type) in ('HQMV', 'HQMF', 'HQMP', 'HQMB') ....then do inner case then do outer case and check....if rated_P_driver_code is null then (coalesce(r9.rated_driver_code,0)) and if c.tr_single_driver IS NULL then (coalesce(c.tr_single_driver,'-')) else leave tr_single_driver as itself

Barry-1604 176 posts Joined 07/05
10 Jul 2007

In that case, you can just repeat your existing code down to where it's different and create another column, like the following:SELECT top 5 (CASE WHEN r9.rated_driver_id Is Not Null AND t9.rated_P_driver_code Is Null AND 'H' || trim(t7.corrected_product_type) in ('HQMV', 'HQMF', 'HQMP', 'HQMB')THEN (CASE WHEN c.trans_seq_no IS NULL AND c.no_qmrneg_det = 'N'THEN NULLELSE (CASE WHEN r9.rated_driver_id IS NOT NULL THEN r9.rated_driver_idWHEN c.Product_Type = 'QMP' THEN r.rated_driver_idELSE NULLEND)END)WHEN t9.rated_P_driver_code IS NULLTHEN (coalesce(r9.rated_driver_code,0)) ELSE NULL END) rated_p_driver_id,(CASE WHEN r9.rated_driver_id Is Not Null AND t9.rated_P_driver_code Is Null AND 'H' || trim(t7.corrected_product_type) in ('HQMV', 'HQMF', 'HQMP', 'HQMB')THEN (CASE WHEN c.trans_seq_no IS NULL AND c.no_qmrneg_det = 'N'THEN NULLELSE (CASE WHEN r9.rated_driver_id IS NOT NULL THEN r9.rated_driver_idWHEN c.Product_Type = 'QMP' THEN r.rated_driver_idELSE NULLEND)END)WHEN t9.rated_P_driver_code IS NULLTHEN (case when c.tr_single_driver IS NULL then (coalesce(c.tr_single_driver,'-')) else c.tr_single_driver end)ELSE NULL END) tr_single_driver,r9.rated_driver_id, t9.rated_P_driver_code, 'H' || trim(t7.corrected_product_type), t2.Policy_id FROM po_risk_detail_cursor c LEFT JOIN tmp_po_rsk_dtl_sales_rep tON c.cursor_id = t.cursor_idDoes this work for you?

marcmc 112 posts Joined 12/05
10 Jul 2007

No but this did..SELECT top 5 (CASE WHEN r9.rated_driver_id Is Not Null AND t9.rated_P_driver_code Is Null AND 'H' || trim(t7.corrected_product_type) in ('HQMV', 'HQMF', 'HQMP', 'HQMB') THEN (CASE WHEN c.trans_seq_no IS NULL AND c.no_qmrneg_det = 'N' THEN NULL ELSE (CASE WHEN r9.rated_driver_id IS NOT NULL THEN r9.rated_driver_id WHEN c.Product_Type = 'QMP' THEN r.rated_driver_id ELSE NULL END) END)WHEN t9.rated_P_driver_code IS NULL THEN (coalesce(r9.rated_driver_code,0)) --, (coalesce(c.tr_single_driver,'-')) ELSE NULL END) rated_p_driver_id, (CASE WHEN rated_p_driver_id /*r9.rated_driver_id*/ = 0 AND t9.rated_P_driver_code Is Null AND 'H' || trim(t7.corrected_product_type) in ('HQMV', 'HQMF', 'HQMP', 'HQMB') THEN (coalesce(c.tr_single_driver,'-')) ELSE c.tr_single_driver END) tr_single_driver /*c.Tr_Single_Driver_id*/ , (CASE WHEN rated_p_driver_id = 0 AND t9.rated_P_driver_code Is Null AND 'H' || trim(t7.corrected_product_type) in ('HQMV', 'HQMF', 'HQMP', 'HQMB') THEN (coalesce(c.mod_policy_holder,'-')) ELSE c.mod_policy_holder END) mod_policy_holder /*c.Tr_Mod_Php_id*/, (CASE WHEN rated_p_driver_id = 0 AND t9.rated_P_driver_code Is Null AND 'H' || trim(t7.corrected_product_type) in ('HQMV', 'HQMF', 'HQMP', 'HQMB') THEN (coalesce(Policy_Holder_Discount,'-')) ELSE Policy_Holder_Discount END) Policy_Holder_Discount /*c.Tr_Php_Discount_id*/, r9.rated_driver_id, t9.rated_P_driver_code, 'H' || trim(t7.corrected_product_type), t2.Policy_id FROM po_risk_detail_cursor c LEFT JOIN tmp_po_rsk_dtl_sales_rep t ON c.cursor_id = t.cursor_id LEFT JOIN tmp_po_rsk_det_policy t3 ON c.policy_code = t3.policy_code LEFT JOIN tmp_po_risk_detail_get_id t2 ON c.cursor_id = t2.cursor_id LEFT JOIN pot_su_pay_method psp ON psp.po_pay_method_code = c.pay_method LEFT JOIN tmp_po_rsk_det_bus_type t4 ON c.cursor_id = t4.cursor_id LEFT JOIN pot_su_sales_rep pssr ON '-' /*sales_rep*/ = pssr.po_sales_rep_code LEFT JOIN tmp_po_rsk_det_postcodes t5 ON c.cursor_id = t5.cursor_id LEFT JOIN tmp_get_cl_version_id t6 ON c.cursor_id = t6.cursor_id LEFT JOIN trt_su_trans_subtype ts ON 'H' || TRIM(TRAILING FROM c.TRANS_TYPE) || (CASE WHEN c.TRANS_TYPE = 'MQ' AND TRIM(TRAILING FROM (CASE WHEN c.reason_code = '0' THEN '' ELSE c.reason_code END ) ) <> '' OR c.TRANS_TYPE = 'MA' AND TRIM(TRAILING FROM (CASE WHEN c.reason_code = '0' THEN '' ELSE c.reason_code END ) ) <> '' OR c.TRANS_TYPE = 'CN' AND TRIM(TRAILING FROM (CASE WHEN c.reason_code = '0' THEN '' ELSE c.reason_code END ) ) <> '' OR c.TRANS_TYPE = 'MC' AND TRIM(TRAILING FROM (CASE WHEN c.reason_code = '0' THEN '' ELSE c.reason_code END ) ) <> '' THEN TRIM(TRAILING FROM c.reason_code) || c.PRODUCT_TYPE ELSE c.TRANS_TYPE END) = ts.tr_sub_type_code LEFT JOIN tmp_po_rsk_det_product_id t7 ON c.cursor_id = t7.cursor_id LEFT JOIN prt_su_product ps ON 'H' || trim(t7.corrected_product_type) || 'H' || trim(t7.corrected_product_class) || trim(t7.corrected_cover_type) || trim(t7.product_group) || trim(t7.pr_sub_cover_class_id) = ps.product_code LEFT JOIN rat_su_rated r ON c.rated_driver = r.rated_driver_code LEFT JOIN tmp_po_rsk_det_bas_premium t8 ON c.cursor_id = t8.cursor_id LEFT JOIN cpt_su_comm_pol_ind csc ON c.cpi_indicator /*comm_pol_ind_code*/ = csc.comm_pol_ind_code LEFT JOIN pot_su_facility psf ON c.facility_num = psf.po_facility_id LEFT JOIN vht_su_vehicle v ON c.vehicle_code = v.vehicle_code --next two tables needed for family discount change LEFT JOIN tmp_rated_p_driver_id t9 ON c.cursor_id = t9.cursor_id LEFT JOIN rat_su_rated r9 ON t9.rated_p_driver_code = r9.rated_driver_code WHERE NOT(c.PRODUCT_CLASS IN ('QMF','QMV','QMP') AND c.TRANS_TYPE IN ('DQ','MQ') AND c.trans_seq_no IS NULL) order by t2.Policy_idThanks for the help.Cheers.

You must sign in to leave a comment.