Avatar of skull52
skull52
Flag for United States of America asked on

Multiple Subqueries in a SQL CASE Statement

I Have a CASE Statement using two subqueries to validate if the Unit price is equal to the Promo price the add the text 'ON ACTIVE PROMO' if the Unit price is NOT equal to the Promo price the add the text 'ON ACTIVE PROMO BUT NOT PROMO PRICE' The code doesn't throw an error, but it only displays the first condition 'ON ACTIVE PROMO'


SELECT     SOP10100.SOPNUMBE AS Invoice_Number, SOP10100.DOCDATE AS Document_Date, SOP10100.CUSTNMBR AS Customer_Number, SOP10100.CUSTNAME AS Customer_Name, SOP10200.ITEMNMBR AS Item_Number, SOP10200.ITEMDESC AS Item_Desc, 
                  UPPER(RTRIM(LTRIM(RM00301.SLPRSNFN))) + ' ' + UPPER(RTRIM(LTRIM(RM00301.SPRSNSLN))) AS Sales_Rep_Name, SOP10200.PRCLEVEL AS Invoice_Item_Pricelevel, SOP10200.UNITPRCE AS Invoice_Unit_Price, IV00108.UOMPRICE AS Pricelevel_Price, 
                  (IV00108.UOMPRICE-SOP10200.UNITPRCE) AS Variance,CASE WHEN SOP10200.ITEMNMBR IN (SELECT     SOP10200.ITEMNMBR FROM        SOP10200 INNER JOIN                   SSGPR100 ON SOP10200.ITEMNMBR = SSGPR100.ITEMNMBR AND SOP10200.UNITPRCE = ROUND(SSGPR100.SSG_Customer_Price, 2) WHERE     (SSGPR100.ENDDATE >= GETDATE())) THEN 'ON ACTIVE PROMO'  WHEN  SOP10200.ITEMNMBR IN  (SELECT     SOP10200.ITEMNMBR FROM        SOP10200 INNER JOIN                   SSGPR100 ON SOP10200.ITEMNMBR = SSGPR100.ITEMNMBR AND SOP10200.UNITPRCE <>ROUND(SSGPR100.SSG_Customer_Price, 2) WHERE     (SSGPR100.ENDDATE >= GETDATE())) THEN 'ON ACTIVE PROMO BUT NOT PROMO PRICE'  ELSE '' END AS Reason , CASE WHEN IV00101.ITEMTYPE = 1 THEN 'SALES ITEM' ELSE 'DISCONTINUED' END AS Item_Type FROM        SOP10100 INNER JOIN                   SOP10200 ON SOP10100.SOPTYPE = SOP10200.SOPTYPE AND SOP10100.SOPNUMBE = SOP10200.SOPNUMBE INNER JOIN                   IV00108 ON SOP10200.ITEMNMBR = IV00108.ITEMNMBR AND SOP10200.PRCLEVEL = IV00108.PRCLEVEL AND SOP10200.UNITPRCE <> IV00108.UOMPRICE INNER JOIN                   IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN                   RM00301 ON SOP10100.SLPRSNID = RM00301.SLPRSNID WHERE     (SOP10100.SOPTYPE = 2) AND (SOP10100.VOIDSTTS <> 1) AND (SOP10200.PRCLEVEL <> '') AND (NOT (SOP10200.PRCLEVEL = 'RETAIL')) AND (IV00101.ITEMTYPE IN (1)) AND (NOT (SOP10100.CUSTNMBR LIKE '%RETAIL%'))AND  SOP10200.ITEMNMBR = 'ETAGLOW201PCGENII               ' ORDER BY Invoice_Number, Document_Date DESC

Open in new window

SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
skull52

8/22/2022 - Mon
ste5an

No, you don't. CASE is an expression, it evaluates to a value.

Then, PLEASE, post always formatted SQL. It took me 5 minutes to do it manually. Formatted SQL is easier to read and understand. Use tables alias names. Especially in this case as you use the same table in different scopes. Avoid unnecessary parentheses.

E.g.

SELECT  S1.SOPNUMBE AS Invoice_Number,
        S1.DOCDATE AS Document_Date,
        S1.CUSTNMBR AS Customer_Number,
        S1.CUSTNAME AS Customer_Name,
        S2.ITEMNMBR AS Item_Number,
        S2.ITEMDESC AS Item_Desc,
        UPPER(RTRIM(LTRIM(R.SLPRSNFN))) + ' ' + UPPER(RTRIM(LTRIM(R.SPRSNSLN))) AS Sales_Rep_Name,
        S2.PRCLEVEL AS Invoice_Item_Pricelevel,
        S2.UNITPRCE AS Invoice_Unit_Price,
        I8.UOMPRICE AS Pricelevel_Price,
        I8.UOMPRICE - S2.UNITPRCE AS [Variance],
        CASE

            WHEN S2.ITEMNMBR IN (
                SELECT  S2I.ITEMNMBR
                FROM    SOP10200 S2I
                    INNER JOIN SSGPR100 SS ON S2I.ITEMNMBR = SS.ITEMNMBR
                                            AND S2I.UNITPRCE = ROUND(SS.SSG_Customer_Price, 2)
                WHERE SS.ENDDATE >= GETDATE()
            )
            THEN 'ON ACTIVE PROMO'

            WHEN  S2.ITEMNMBR IN (
                    SELECT  S2I.ITEMNMBR
                    FROM    SOP10200 S2I
                        INNER JOIN SSGPR100 SS ON S2I.ITEMNMBR = SS.ITEMNMBR
                                                AND S2I.UNITPRCE <> ROUND(SS.SSG_Customer_Price, 2)
                    WHERE SS.ENDDATE >= GETDATE()
                )
                THEN 'ON ACTIVE PROMO BUT NOT PROMO PRICE'

            ELSE ''
        END AS Reason ,
        CASE WHEN I1.ITEMTYPE = 1
            THEN 'SALES ITEM'
            ELSE 'DISCONTINUED'
        END AS Item_Type
FROM    SOP10100 S1
    INNER JOIN SOP10200 S2 ON S2.SOPTYPE = S2.SOPTYPE
                                AND S1.SOPNUMBE = S2.SOPNUMBE
    INNER JOIN IV00108 I8 ON S2.ITEMNMBR = I8.ITEMNMBR
                                AND S2.PRCLEVEL = I8.PRCLEVEL
                                AND S2.UNITPRCE <> I8.UOMPRICE
    INNER JOIN IV00101 I1 ON S2.ITEMNMBR = I1.ITEMNMBR
    INNER JOIN RM00301 R ON S1.SLPRSNID = R.SLPRSNID
WHERE   S1.SOPTYPE = 2
    AND S1.VOIDSTTS <> 1
    AND S2.PRCLEVEL <> ''
    AND S2.PRCLEVEL != 'RETAIL'
    AND I1.ITEMTYPE = 1
    AND NOT S1.CUSTNMBR LIKE '%RETAIL%'
    AND  S2.ITEMNMBR = 'ETAGLOW201PCGENII               '
ORDER BY Invoice_Number,
    Document_Date DESC;

Open in new window

Now you can clearly see, that your subqueries are not correlated to the outer query. Thus you should review their results first, cause using ROUND in a JOIN condition may not work as intended. Without knowing the data model, you may do unprecise float arithmetic instead of precise numeric.
Then, how are the cardinalities? Cause if it's 1:1, then you can use a JOIN instead of subqueries. When not, an EXISTS instead of an IN predicate should be faster. E.g.

SELECT  S1.SOPNUMBE AS Invoice_Number,
        S1.DOCDATE AS Document_Date,
        S1.CUSTNMBR AS Customer_Number,
        S1.CUSTNAME AS Customer_Name,
        S2.ITEMNMBR AS Item_Number,
        S2.ITEMDESC AS Item_Desc,
        UPPER(RTRIM(LTRIM(R.SLPRSNFN))) + ' ' + UPPER(RTRIM(LTRIM(R.SPRSNSLN))) AS Sales_Rep_Name,
        S2.PRCLEVEL AS Invoice_Item_Pricelevel,
        S2.UNITPRCE AS Invoice_Unit_Price,
        I8.UOMPRICE AS Pricelevel_Price,
        I8.UOMPRICE - S2.UNITPRCE AS [Variance],
        CASE

            WHEN EXISTS(
                SELECT  *
                FROM    SOP10200 S2I
                    INNER JOIN SSGPR100 SS ON S2I.ITEMNMBR = SS.ITEMNMBR
                                            AND S2I.UNITPRCE = ROUND(SS.SSG_Customer_Price, 2)
                WHERE SS.ENDDATE >= GETDATE()
                    AND S2I.ITEMNMBR = S2.ITEMNMBR
            )
            THEN 'ON ACTIVE PROMO'

            WHEN NOT EXISTS(
                    SELECT  S2I.ITEMNMBR
                    FROM    SOP10200 S2I
                        INNER JOIN SSGPR100 SS ON S2I.ITEMNMBR = SS.ITEMNMBR
                                                AND S2I.UNITPRCE = ROUND(SS.SSG_Customer_Price, 2)
                    WHERE SS.ENDDATE >= GETDATE()
                        AND S2I.ITEMNMBR = S2.ITEMNMBR
                )
                THEN 'ON ACTIVE PROMO BUT NOT PROMO PRICE'

            ELSE ''
        END AS Reason ,
        CASE WHEN I1.ITEMTYPE = 1
            THEN 'SALES ITEM'
            ELSE 'DISCONTINUED'
        END AS Item_Type
FROM    SOP10100 S1
    INNER JOIN SOP10200 S2 ON S2.SOPTYPE = S2.SOPTYPE
                                AND S1.SOPNUMBE = S2.SOPNUMBE
    INNER JOIN IV00108 I8 ON S2.ITEMNMBR = I8.ITEMNMBR
                                AND S2.PRCLEVEL = I8.PRCLEVEL
                                AND S2.UNITPRCE <> I8.UOMPRICE
    INNER JOIN IV00101 I1 ON S2.ITEMNMBR = I1.ITEMNMBR
    INNER JOIN RM00301 R ON S1.SLPRSNID = R.SLPRSNID
WHERE   S1.SOPTYPE = 2
    AND S1.VOIDSTTS <> 1
    AND S2.PRCLEVEL <> ''
    AND S2.PRCLEVEL != 'RETAIL'
    AND I1.ITEMTYPE = 1
    AND NOT S1.CUSTNMBR LIKE '%RETAIL%'
    AND  S2.ITEMNMBR = 'ETAGLOW201PCGENII               '
ORDER BY Invoice_Number,
    Document_Date DESC;

Open in new window


p.s. instead of RTRIM(LTRIM(R.SLPRSNFN)) you should have either a CONSTRAIN on those columns or a data cleansing task or trigger instead.
skull52

ASKER
ste5an,
Thanks however the 'ON ACTIVE PROMO BUT NOT PROMO PRICE' is still not working. The promo price for this item is $79.95000 but the unit Price in the image below is $94.95000 therefore the reason column value from the CASE statement should be  'ON ACTIVE PROMO BUT NOT PROMO PRICE' 
skull52

ASKER
Sorry forgot to attach image 
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
PortletPaul

As this is for MS SQL Server you could use an OUTER APPLY e.g.
SELECT
      *
    , CASE WHEN oa.ITEMNMBR IS NOT NULL 
                THEN 'ON ACTIVE PROMO'
                ELSE 'ON ACTIVE PROMO BUT NOT PROMO PRICE'
           END AS Reason
FROM SOP10100 S1
INNER JOIN SOP10200 S2 ON S2.SOPTYPE = S2.SOPTYPE
                    AND S1.SOPNUMBE = S2.SOPNUMBE
OUTER APPLY (SELECT TOP(1)
              S2I.ITEMNMBR
            FROM SOP10200 S2I
            INNER JOIN SSGPR100 SS ON S2I.ITEMNMBR = SS.ITEMNMBR
                                AND S2I.UNITPRCE = ROUND(SS.SSG_Customer_Price, 2)
            WHERE SS.ENDDATE >= GETDATE()
            AND S2I.ITEMNMBR = S2.ITEMNMBR
            -- ORDER BY SS.ENDDATE DESC ??? 
            ) AS oa
INNER JOIN IV00108 I8 ON S2.ITEMNMBR = I8.ITEMNMBR
                    AND S2.PRCLEVEL = I8.PRCLEVEL
                    AND S2.UNITPRCE <> I8.UOMPRICE
INNER JOIN IV00101 I1 ON S2.ITEMNMBR = I1.ITEMNMBR
INNER JOIN RM00301 R ON S1.SLPRSNID = R.SLPRSNID

Open in new window

strictly speaking use of TOP(1) requires use of an ORDER BY but I didn't want to suggest what to use [although enddate is a candidate] and as it only the existence of any match that matters so ordering could be omitted.
PortletPaul

Taking another look at this, I SUSPECT that you PROBABLY need a way choose the MOST RECENT promotion and that's why you are not currently getting correct/predictable results. Note all of this is a guess, particularly as we don't have either "sample data"or "expected results" to work with (see note below).

So with, with some assumptions involved, here is a way to get the most recent date and price
SELECT
      *
    , CASE WHEN promo.ENDDATE >= GETDATE() AND SOP10200.UNITPRCE = promo.SSG_Customer_Price 
                THEN 'ON ACTIVE PROMO'
           WHEN promo.ENDDATE >= GETDATE() AND SOP10200.UNITPRCE <> promo.SSG_Customer_Price 
                THEN 'ON ACTIVE PROMO BUT NOT PROMO PRICE'
           ELSE ''
           END AS Reason
FROM SOP10100 S1
INNER JOIN SOP10200 S2 ON S2.SOPTYPE = S2.SOPTYPE
                    AND S1.SOPNUMBE = S2.SOPNUMBE
LEFT JOIN (
      SELECT
          SOP10200.ITEMNMBR
        , SSGPR100.ENDDATE
        , ROUND(SSGPR100.SSG_Customer_Price, 2) AS SSG_Customer_Price
        , row_number() over(partition by SOP10200.ITEMNMBR
                            order by SSGPR100.ENDDATE DESC) as rn
      FROM SOP10200
      INNER JOIN SSGPR100 ON SOP10200.ITEMNMBR = SSGPR100.ITEMNMBR
      ) AS promo ON promo.rn = 1 AND SOP10200.ITEMNMBR = promo.ITEMNMBR

      /* returns only one row per itemnmbr so should not expand total rows of query */

INNER JOIN IV00108 I8 ON S2.ITEMNMBR = I8.ITEMNMBR
                    AND S2.PRCLEVEL = I8.PRCLEVEL
                    AND S2.UNITPRCE <> I8.UOMPRICE
INNER JOIN IV00101 I1 ON S2.ITEMNMBR = I1.ITEMNMBR
INNER JOIN RM00301 R ON S1.SLPRSNID = R.SLPRSNID


Open in new window


Tip: The fastest way to get an accurate result from questions such as this is to provide some data (for each table) so that we can actually run a query; and, also provide the "expected result" you need (using just the sample data). This way we can produce a solution without the guesswork and the overall time to solution is reduced.

ste5an

Then, how are the cardinalities? Cause if it's 1:1, then you can use a JOIN instead of subqueries.
You need to explain your data model..
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
skull52

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question