question

fruit_ninja11222 avatar image
fruit_ninja11222 asked

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)))

error-messageerror
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ankushparab avatar image
ankushparab answered

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

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.