question

ChristopherJack-1763 avatar image
0 Votes"
ChristopherJack-1763 asked ·

Using a select in a case statement

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


sql-server-transact-sql
· 3
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you provide more details or post your code so that we can understand what you want to do? You may not need the CASE statement to find out the duplicates.

0 Votes 0 ·

Thanks for the reply.. i have updated the question with the full sql.

I need to test the table to see if the order number is in more than once and if it is then to add a 'A'



0 Votes 0 ·
MelissaMa-msft avatar image MelissaMa-msft ChristopherJack-1763 ·

Hi @ChristopherJack-1763,

Please check whether Guoxiong's latest answer is working.

If no, please provide some sample data for all related tables and expected outout.

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered ·

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 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That works perfectly thank you

0 Votes 0 ·
OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered ·

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',
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
1 Vote"
cooldadtx answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered ·

Hi @ChristopherJack-1763,

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
1 Vote"
cooldadtx answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.