CASE Statement EXISTS Query Check Assist

Transact-SQL
1

Hey all,

Hoping someone can assist with how I can do this best. I have a query that will try to get a record value based on conditions but if it does not return a value I need to change part of the query to use another field. Here is what I am trying to get at syntax is not correct here but trying to show what I am looking for:

SELECT c.CUSTOMER_AMT,
CASE WHEN EXISTS(SELECT TOP 1.PRICE FROM CUST_RATE
WHERE CUST_CURRENCY = c.LOCAL_CURRENCY) AS PRICE,
(c.QTY * [PRICE FROM THE CASE STATEMENT ABOVE]) * 100 AS AMOUNT
FROM CUSTOMER c

What I need to do is if that sub query in the CASE does not get a record I need to change the CUST_CURRENCY equals to c.FOREIGN_CURRENCY this to get the PRICE as I need it in the next calculation AMOUNT:

SELECT TOP 1.PRICE FROM CUST_RATE
WHERE CUST_CURRENCY = c.FOREIGN_CURRENCY

Not sure why I am struggling to see how to do this. Hope this is somewhat clear and thanks for any help :slight_smile:

2

There are a number of ways to do this, here's one:


SELECT c.CUSTOMER_AMT, c.QTY * ISNULL(CRL.PRICE, CRF.PRICE) * 100 AS AMOUNT
FROM CUSTOMER c
OUTER APPLY (SELECT TOP (1) PRICE FROM CUST_RATE 
    WHERE CUST_CURRENCY = c.LOCAL_CURRENCY) AS CRL
OUTER APPLY (SELECT TOP (1) PRICE FROM CUST_RATE 
    WHERE PL.PRICE IS NULL AND CUST_CURRENCY = c.FOREIGN_CURRENCY) AS CRF
2 Likes
3

Thanks that works for me