CASE statement with AND and THEN condition question

Transact-SQL
1

Hi

I have a CASE statement referencing two tables. (this is for a stored procedure).

CASE
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
ELSE Infoportal.dbo.fn_Generic_ProperCase(COALESC(fin_ent.fin_enty_desc,i.fld2_desc,bbg.issuer))
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

2

The line above your highlighted line has 2 ending brackets in the "in" section.

If this is just a typo when you created this thread, then we need the following to help you further:

  • table descriptions in the form of create statements
  • sample data in the form of insert statements
  • expected output from the sample data you provide
  • show the entire query (snipets can hide the reason, why you don't get your expected result)
3

yes typo.. apologies.

c = CUST table
i = ISSUE table

I have a join:
LEFT OUTER JOIN InfoPortal.CUST AS c
ON c.CUST_MNEM = i.FLD1_TXT

I need to look up the CUST_LNG_NME value in the CUST table using the CUST_MNEM value which corresponds with the FLD1_TXT value in the ISSUE table.
The problem is for each customer record in CUST there are multiple records as they have different CUST_TYP (e.g. for CUST_MNEM "ABC" there are 3 records in the CUST table as ABC has a record for CUST_TYP 'TFOP', 'TFOP_EC' and 'TFOP_ER' All 3 of them have a slightly different CUST_LNG_NME but they all have the same CUST_MNEM. I need to return the CUST_LNG_NME associated with the CUST_TYP 'TFOP'

4

What are the tables names are involved in this query?

5

dbo.CUST (c)
dbo.ISSUE_DG (i)

6

now provide sample data and schema for these tables

create table #CUST(CUST_LNG_NME varchar(50))
create table #ISSUE_DG(issue_cls2_cde varchar(50), 
exch_mnem  varchar(50), iss_typ varchar(50))

insert into #CUST
7

A case expression will stop when it finds a match - so if you know there is a row that has the qualifying values where it should have matched and you got a different value then one of the previous conditions was true - or the values you are checking for did not match.

With that said - what value do you want to return if the CUST_MNEM is not equal to 'TFOP'? Or - do you only want the rows that have a CUST_MNEM equal to 'TFOP' to be included?

8

Maybe easier to explain in the below example:

select CUST_MNEM, CUST_LNG_NME, CUST_TYP FROM CUST
WHERE CUST_MNEM = 'Absa'

qw
qw.png1152×648 30.2 KB

So in my case statement I want to use the FLD1_TXT value in the ISSUE_DG table (i) which corresponds with the CUST_MNEM value in CUST (c) and return the CUST_LNG_NME value of the CUST_TYP 'TFOP' and not the CUST_LNG_NME where the CUST_TYP is 'TFOP_EC'. So in the above example I want ABSA Bank and not ABSA Corporate to return.

9

In your example - I only see the table/data from CUST. If there is supposed to be something else - it did not post.

I am not able to follow your narrative - really need sample data (in consumable insert statements) so I can actually run queries on my systems. Please provider table definitions and insert statements with sample data.

I can only guess - but it seems you want 'ABSA Bank' for the row in your picture that currently has 'ABSA Corporate'. I don't see how that is possible because the CUST_TYP for that row is TFOP_EC.