0
select  max(Contract_Num) as contract_num,
        max(Lic_Plate_Num) as lic_plate_num,
        max(cast(Revision_Eff_Date as date)) as rev_eff_date, 
        max(contract_name) as cn_name,
        max(CASE WHEN product_code = 'CSL' THEN Limit_1 END) as value_1 ,
        max(CASE WHEN c.product_code in ('PIP','FPB') THEN Limit_1 END) as value_2,
        max(CASE WHEN product_code = 'MEDPM' THEN Limit_1 END) as value_3,
        max(CASE WHEN c.product_code in ( 'UMUIM','UMISG','UMCSL') THEN Limit_1 END) as value_4,
        max(CASE WHEN c.product_code in ( 'UNCSL','UNDSG') THEN Limit_1 END) as value_5         
from [dba].[Contract] as a
left join [dba].[CDAA_Vehicle] as b on a.Contract_ID=b.Contract_ID
left join [dba].[Contract_Product] as c on c.Contract_ID=a.Contract_ID
left join dba.Contract_Name as d on c.Contract_ID = d.Contract_ID
left join dba.Contract_Article ca on c.Contract_ID = ca.Contract_ID
where ca.Status!='D'

and the output is

contract_num  lic_plate_num  rev_eff_date  cn_name value_1 value_2 value_3 value_4 value_5 
1231           V24B32        12-09-2022    test     null    null    100    200      367  

This is my query and output, here I'm selecting limit_1 value based on product and the limit_1 value may or may not be null. but here I want to print one message 'Included' for individual product limit_1, when the limit_1 value is not null.

2
  • 2
    Are you sure you are using MySQL ? [dba].[Contract] isn't valid in MySQL Aug 1 at 8:47
  • COALESCE(limit_1, "Included")
    – Rick James
    Aug 1 at 16:57

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.