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'
The SQL NOT EXISTS Operator will perform quite opposite to the EXISTS Operator. It is helpful in restricting the number of rows returned by the SELECT Statement. The 'NOT EXISTS' operator in SQL Server will check the Subquery for rows existence. If there are no rows then it will return TRUE, otherwise FALSE.
NOT EXISTS is used with a subquery in the WHERE clause to check if the result of the subquery returns TRUE or FALSE. The Boolean value is then used to narrow down the rows from the outer select statement.
An alternative for IN and EXISTS is an INNER JOIN, while a LEFT OUTER JOIN with a WHERE clause checking for NULL values can be used as an alternative for NOT IN and NOT EXISTS.
The Case-When-Exists expression in Oracle is really handy. Here's an example of how to use it in a sub-select to return a status. This SQL checks for a match between the PS_PERSON and PSOPRDEFN records to determine the person status.
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With