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