Databases
StackOverflow2013
Postsdbo
POSQL How to use CASE with a NOT EXISTS statement
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL How to use CASE with a NOT EXISTS statement
    text
    copied!<p>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. </p> <p>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. </p> <p>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. </p> <pre><code>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' </code></pre> <p>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!</p> <pre><code>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' </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload