Error Msg 537.Invalid length parameter passed to the LEFT or SUBSTRING function.
This is what I wrote. Some [Item] are named "Misc. Adjustment (Miscellaneous Adjustments)", "Misc. Adjustment (Miscellaneous Adjustments)", and "P:Tools:Salon:MANN1092 (Practice Mannequin 14")"
I am trying to delete the additional information in the parenthesis.
With
TempSalesOrder as
(SELECT
CASE WHEN CHARINDEX('(',[item]) > 0
THEN TRIM(LEFT([item],CHARINDEX('(',[item])-1))
ELSE Trim([Item])
END as 'Trimmed_Name',
SUM(QTY) as 'Total_Sum'
FROM temp.QB_SO
GROUP BY [item], TRIM(LEFT([item],CHARINDEX('(',[item])-1)))
It seems, Data in item column in some of the rows does not have '('. you have added CASE in SELECT clause however GROUP BY clause does not have that condition check; which is where its most probably throwing error. you can re-write the code as below.
WITH
TempSalesOrder as
(
SELECT
CASE WHEN CHARINDEX('(',[item]) > 0
THEN TRIM(LEFT([item],CHARINDEX('(',[item])-1))
ELSE Trim([Item])
END as 'Trimmed_Name',
QTY
FROM QB_SO
)
SELECT [Trimmed_Name], SUM(QTY)
FROM TempSalesOrder
GROUP BY Trimmed_Name
