Using a select in a case statement

Christopher Jack 1,611 Reputation points
2021-02-26T15:37:30.253+00:00

Hi,

I am wanting to use select in a case statement to see if an order number already exists in a table more than once.

I have the following code.

Case 
When
 salescount = (select count(fsa.OrderNumber) from [Chalgrove].[FactSalesOrder] fsa where fsa.OrderNumber = ci.Sales_Document_num) > 1
then 'A'
else
 ''
end as 'Duplicate action',

It is erroring with the error message Incorrect syntax near '>'.

Any help appreciated

Full SQL is

SELECT Distinct
    'D' as 'Record_Type',
    ci.Sales_Document_num as 'Order_Reference',
    ci.Sales_Document_num as 'OrderNumber',
    d.FullDate as 'OrderDate',
    ci.Delivery_name as 'delivery_contact_name',
    '' as 'Company_Name',
    ci.Address1 as 'delivery_addressline1',
    ci.Address2 as 'delivery_addressline2',
    ci.Address4 as 'City',
    CAST(CHAR(39) + ci.Postcode as nvarchar) as 'delivery_post_code',
    ci.Address5 as 'County',
    ci.country_code as 'delivery_country_code',
    ci.Telephone as 'delivery_contact_number',
    ci.email as 'email',
    ci.Currency_code,
    '' as 'Export_AWB',
    'DHL' as 'Export_Carrier_Name',
     Case 
 When
    (select count(fsa.OrderNumber) from [Chalgrove].[FactSalesOrder] fsa where cast(fsa.OrderNumber as int) = ci.Sales_Document_num) > 1
 then 'A'
 else
     ''
 end as 'Duplicate action',
    '' as 'RMA',
    '' as 'Export_Date',
    '' as 'Free Return',
    ci.address3 as 'delivery_addressline3',
    '' as 'Suberb',
    '' as 'Neighborhood',
    '' as 'Custom_field_1',
    '' as 'Custom_field_2',
    '' as 'Custom_field_3',
    '' as 'Custom_field_4',
    '' as 'Custom_field_5'
FROM 
    #ReboundInfo ci
        LEFT JOIN [Chalgrove].[FactSalesOrder] so ON cast(ci.Sales_Document_num as nvarchar) = cast(so.OrderNumber as nvarchar)
        LEFT JOIN [dbo].[DimDate] d ON so.OrderDateKey = d.DateKey
GROUP BY
    ci.Sales_Document_num,
    ci.Order_Date,
    ci.Delivery_name,
    ci.Address1 ,
    ci.Address2 ,
    ci.Address4,
    ci.Postcode,
    ci.Address5,
    ci.country_code,
    ci.Telephone,
    ci.email,
    ci.Currency_code,
    ci.address3,
    d.FullDate
ORDER BY
    ci.Sales_Document_num
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions
Accepted answer
  1. Guoxiong 8,126 Reputation points
    2021-03-01T15:23:02.447+00:00

    Try this:

    SELECT Distinct
     'D' as 'Record_Type',
     ci.Sales_Document_num as 'Order_Reference',
     ci.Sales_Document_num as 'OrderNumber',
     d.FullDate as 'OrderDate',
     ci.Delivery_name as 'delivery_contact_name',
     '' as 'Company_Name',
     ci.Address1 as 'delivery_addressline1',
     ci.Address2 as 'delivery_addressline2',
     ci.Address4 as 'City',
     CAST(CHAR(39) + ci.Postcode as nvarchar) as 'delivery_post_code',
     ci.Address5 as 'County',
     ci.country_code as 'delivery_country_code',
     ci.Telephone as 'delivery_contact_number',
     ci.email as 'email',
     ci.Currency_code,
     '' as 'Export_AWB',
     'DHL' as 'Export_Carrier_Name',
     CASE WHEN COUNT(fsa.OrderNumber) > 1 THEN 'A' ELSE '' END AS 'Duplicate action',
     '' as 'RMA',
     '' as 'Export_Date',
     '' as 'Free Return',
     ci.address3 as 'delivery_addressline3',
     '' as 'Suberb',
     '' as 'Neighborhood',
     '' as 'Custom_field_1',
     '' as 'Custom_field_2',
     '' as 'Custom_field_3',
     '' as 'Custom_field_4',
     '' as 'Custom_field_5'
 FROM 
     #ReboundInfo ci
        INNER JOIN [Chalgrove].[FactSalesOrder] AS fsa ON CAST(fsa.OrderNumber AS int) = ci.Sales_Document_num
         LEFT JOIN [Chalgrove].[FactSalesOrder] so ON cast(ci.Sales_Document_num as nvarchar) = cast(so.OrderNumber as nvarchar)
         LEFT JOIN [dbo].[DimDate] d ON so.OrderDateKey = d.DateKey
 GROUP BY
     ci.Sales_Document_num,
     ci.Order_Date,
     ci.Delivery_name,
     ci.Address1 ,
     ci.Address2 ,
     ci.Address4,
     ci.Postcode,
     ci.Address5,
     ci.country_code,
     ci.Telephone,
     ci.email,
     ci.Currency_code,
     ci.address3,
     d.FullDate
 ORDER BY
     ci.Sales_Document_num
    1 person found this answer helpful.

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,901 Reputation points
    2021-02-26T15:45:51.557+00:00

    What's the "salescount = " good for? Intended as alias? Remove it. 

     Case 
 When
     (select count(fsa.OrderNumber) from [Chalgrove].[FactSalesOrder] fsa where fsa.OrderNumber = ci.Sales_Document_num) > 1
 then 'A'
 else
     ''
 end as 'Duplicate action',
    1 person found this answer helpful.
    0 comments
  2. Michael Taylor 48,581 Reputation points
    2021-02-26T15:53:07.897+00:00

    Can you do a select inside a case statement? Yes. Is it a good idea? Probably not. I suspect performance will be bad. I think there are better ways to solve this problem that don't require a select on every row in your outer table. Using a group by/having or equivalent I think might be better but I'm not a SQL guru so I will leave it to others to provide a better recommendation.

    In terms of the error the issue is that you're using the WHEN that is using equality but the right sides of the equality is a boolean expression (relational ops are always boolean) so it cannot convert the expression. I'm assuming here that salescount is an integral value. Without knowing what SalesCount actually is I assume you are expecting the count of the orders in the ci table to match the salescount column so simple equality works here. 

    DECLARE @FactSalesOrder TABLE (OrderNumber VARCHAR(100), SalesCount INT) 
INSERT INTO @FactSalesOrder VALUES ('123', 20), ('456', 1)

DECLARE @SalesDocument TABLE (Sales_Document_Num VARCHAR(100))
INSERT INTO @SalesDocument VALUeS ('123'), ('789')

SELECT 
 Case 
 When
     salescount = (select count(fsa.OrderNumber) from @FactSalesOrder fsa where fsa.OrderNumber = ci.Sales_Document_num)
 then 'A'
 else
     ''
 end as 'Duplicate action', *
 FROM @FactSalesORder o JOIN @SalesDocument ci ON o.OrderNumber = ci.Sales_Document_num

    This would set the column to A if they are equal. Of course if you want the opposite then switch to not equal.

    1 person found this answer helpful.
    0 comments
  3. MelissaMa-MSFT 24,176 Reputation points
    2021-03-01T01:50:41.023+00:00

    Hi @Christopher Jack ,

    Agreed with other experts, you could consider to remove the "salescount" from your case when statement if you also need to do a comparison using '>'.

    Besides, you could also use IIF function instead of case when statement as below if necessary: 

     IIF((select count(fsa.OrderNumber) from [Chalgrove].[FactSalesOrder] fsa where fsa.OrderNumber = ci.Sales_Document_num) > 1,'A','') as 'Duplicate action',

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments
  4. Michael Taylor 48,581 Reputation points
    2021-03-01T14:47:03.223+00:00

    A first pass optimization, to me, would be to get rid of all those group-bys. I suspect you put them in because you got errors saying you cannot select from the query without the group by. You can fix that by only using group by on a subquery (or join) to detect the dups and then select the columns from the higher level select.

    Here's the test query I used to simplify down your original query. 

    DECLARE @FactSalesOrder TABLE (OrderNumber INT, OrderDateKey VARCHAR(100)) 
DECLARE @DimDate TABLE (DateKey VARCHAR(100), FullDate DATETIME)
DECLARE @ReboundInfo TABLE (Sales_Document_num INT, Delivery_Name VARCHAR(100), Order_Date VARCHAR(100))

INSERT INTO @FactSalesOrder VALUES (1, '02/28/2021'), (2,'03/01/2021')
INSERT INTO @DimDate VALUES ('02/28/2021', '02/28/2021'), ('03/01/2021','03/01/2021')
INSERT INTO @ReboundInfo VALUES (1,'D1','02/28/2021'), (1, 'D1_2', '03/01/2021'), (2, 'D2_1', '03/01/2021')

SELECT 
   'D' AS Record_Type,
   ci.Sales_Document_num AS Order_Reference,
   so.OrderNumber,
   d.FullDate AS OrderDate,
   ci.Delivery_Name AS delivery_contact_name,
   CASE WHEN dups.OrderNumber IS NOT NULL THEN 'A' ELSE '' END AS 'Duplicate action'
FROM 
  @FactSalesOrder so
  JOIN @ReboundInfo ci ON CAST(so.OrderNumber AS NVARCHAR) = CAST(ci.Sales_Document_num AS NVARCHAR)
  JOIN @DimDate d ON d.DateKey = so.OrderDateKey
  LEFT JOIN (SELECT OrderNumber FROM @FactSalesOrder GROUP BY OrderNumber HAVING COUNT(*) > 1) dups ON dups.OrderNumber = ci.Sales_Document_num

    The above test data doesn't have any dups when run against your original query and they don't produce any dups here. This query removes the extra columns that didn't have an impact on the query rewrite and takes guesses on the table structures by using table variables to inject the data of interest.

    Again, not a SQL guru, but it should be faster than your original query at the cost of finding all dups in FactSalesOrder. Of course if this table is large the query may be slow so doing additional filtering could be useful. Looking at the exec plan would be useful. You could also consider using a temp table if it provides to be faster but this would be my first pass.

    If it doesn't work for you then please provide sample data to test against.

    1 person found this answer helpful.
    0 comments