question

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',

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.

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.

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.