I have a CASE statement referencing two tables. (this is for a stored procedure).
WHEN i.issue_cls2_cde = '051' AND i.exch_mnem = 'XSAF' THEN 'SAFEX'
WHEN i.issue_cls2_cde = '051' AND i.exch_mnem = 'YLDX' THEN 'YIELDX'
WHEN i.iss_typ IN ('OED','ETF','ITS','PCI','INS') THEN NULL
WHEN i.iss_typ IN ('IRS','TRS','OIS','CCS', 'ZCVN')) THEN i.iss_nme
WHEN i.iss_typ IN ('COD','CPS','VCD','FCD') AND c.CUST_TYP = 'TFOP' THEN c.CUST_LNG_NME
END AS sIssuerLongName
With reference to the condition in Bold - I have multiple records in the c table where there are different CUST_TYP records. E.g. for a specific customer there are multiple records based on CUST_TYP "TFOP" , "TFOP_EC" , "TFOP_ER" etc. What I am trying to achieve is to only return the c.CUST_LNG_NME for the record in the line where the CUST_TYP is "TFOP" and not all the others.
I think my current CASE statement ignores the bold line altogether and skip to the ELSE. I do have a join clause:
LEFT OUTER JOIN InfoPortal..CUST AS c
ON c.CUST_MNEM = i.FLD1_TXT