Case Statement 2

UDA
Enthusiast

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
.....
4 REPLIES
Enthusiast

Re: Case Statement 2

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 NULL
THEN (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
END

The '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!
Enthusiast

Re: Case Statement 2

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

Enthusiast

Re: Case Statement 2

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 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))
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 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 (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 t
ON c.cursor_id = t.cursor_id

Does this work for you?

Enthusiast

Re: Case Statement 2

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_id

Thanks for the help.
Cheers.