CODEWITHSUNDEEP
sql-serverexists
user2599240
user2599240

Reputation: 101

SQL How to use CASE with a NOT EXISTS statement

I have quite a complex query in SQL where I am pulling a load of information from various tables and doing various joins. I want to pull a column which checks whether the particular tradeId is contained in 2 different tables. I'm getting stuck with how to do this properly though.

The below code gives me all the TradeIds from the Trade table that are NOT in the TCM (which is just a combination of 2 tables). However I would like ALL the trades from the Trade table and then a column to indicate whether it is found in TCM or not.

I know this would be done with a CASE WHEN query but I'm confused how to structure it so that it fits in the CASE WHEN. 

With subCA As
(Select distinct OTPTradeId, ConfoAuditSenderRef from ConfirmationAudit where ConfoAuditSenderRef like 'HBEUM%'),
TCM As
(Select distinct OTPTradeID from subCA union ALL select TradeId from subCA inner join ConfirmationSent on (OTPTradeId = ConfoId 
AND ConfoAuditSenderRef like 'HBEUMN%'))
select TradeId from Trade where NOT EXISTS (Select OtpTradeId from TCM where OtpTradeId = TradeId) 
and TradeDate = '17 jun 2013'

Heres my attempt to fit it in a CASE WHEN statement but I get an error because the NOT EXISTS is not allowed without a WHERE I believe. But what I am after is something like this. If I use NOT IN it becomes painstakingly slow like 5 minutes plus and this is part of an larger query and I dont want it to take so long - if possible!

With subCA As
(Select distinct OTPTradeId, ConfoAuditSenderRef from ConfirmationAudit where ConfoAuditSenderRef like 'HBEUM%'),
TCM As
(Select distinct OTPTradeID from subCA union ALL select TradeId from subCA inner join ConfirmationSent on (OTPTradeId = ConfoId 
AND ConfoAuditSenderRef like 'HBEUMN%'))
select TradeId,
CASE WHEN
    (TradeId NOT EXISTS (Select OtpTradeId from TCM where OtpTradeId = TradeId) Then 'Y' Else 'N' End As 'TCM' 
from Trade
WHERE TradeDate = '17 jun 2013'

Upvotes: 9

Views: 68682

Answers (3)

Adriaan Stander
Adriaan Stander

Reputation: 166486

Change the part

TradeId NOT EXISTS

to 

TradeId NOT IN

Have a look at the difference between EXISTS (Transact-SQL) and IN (Transact-SQL)

Have a look at this small example

SQL Fiddle DEMO

Further to that, maybe revisit the Syntax of CASE (Transact-SQL)

Simple CASE expression: 
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 
Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

Upvotes: 4

Devart
Devart

Reputation: 122002

Try this one -

SELECT
       t.TradeId
     , CASE WHEN NOT EXISTS (
          SELECT 1
          FROM TCM t2
          WHERE t2.OtpTradeId = t.TradeId
      ) Then 'Y' Else 'N' END As 'TCM' 
FROM Trade t
WHERE t.TradeDate = '17 jun 2013'

Upvotes: 12

Novice
Novice

Reputation: 558

You can use NOT IN. Try according to your need.

Upvotes: 0

Related Questions