Mark Wilson
asked on
Invalid length parameter
I have the following query I am using - it throws up the following error message
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
Can anybody help?
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
Can anybody help?
;WITH [WL] AS (
SELECT [L].[LookupId],
[L].[LookupRuleSet],
[L].[LookupVariety],
[L].[LookupType],
[L].[LookupValue],
REPLACE([L].[LookupString], '[' + [SV].[CA] + ']', '') [LookupString],
[SV].[C] [CaseCategory]
FROM [Meta].[Lookup] [L]
LEFT OUTER JOIN (
SELECT [CS].[LookupId],
[CS].[CA],
[SPLIT].[CS].value('.', 'INT') AS [C]
FROM (
SELECT [L].[LookupId],
LEFT(RIGHT([L].[LookupString], LEN([L].[LookupString]) - PATINDEX('%[[]%', [L].[LookupString])), PATINDEX('%]%', RIGHT([L].[LookupString], LEN([L].[LookupString]) - PATINDEX('%[[]%', [L].[LookupString]))) - 1) [CA],
CAST('<XL>' + REPLACE(LEFT(RIGHT([L].[LookupString], LEN([L].[LookupString]) - PATINDEX('%[[]%', [L].[LookupString])), PATINDEX('%]%', RIGHT([L].[LookupString], LEN([L].[LookupString]) - PATINDEX('%[[]%', [L].[LookupString]))) - 1), ',', '</XL><XL>') + '</XL>' AS XML) AS [C]
FROM [Meta].[Lookup] [L]
) AS [CS] CROSS APPLY [C].nodes ('/XL') AS SPLIT([CS])
) AS [SV] ON [L].[LookupId] = [SV].[LookupId]
)
UPDATE [Motor].Matter
SET WorkSource = CASE WHEN [WL].[LookupValue] IS NULL THEN 'Others'
WHEN [Matter].[System] = 'IMELEgis' THEN 'IMe'
ELSE [WL].[LookupValue]
END
FROM [Motor].[Matter] [Matter]
LEFT OUTER JOIN [Motor].[Client] cl ON cl.ClaimID = Matter.ClaimID
LEFT OUTER JOIN [WL] ON [Matter].[CaseType]
+ COALESCE([Matter].[NUScheme], '') LIKE CASE
WHEN [WL].[LookupType] IN (
'Endswith',
'Wildcard' )
THEN '%'
ELSE ''
END
+ REPLACE([WL].[LookupString], '||', '%')
+ CASE WHEN [WL].[LookupType] IN (
'Startswith', 'Wildcard' )
THEN '%'
ELSE ''
END
AND [cl].[MotorCostLienCat2] = [WL].[CaseCategory]
AND [WL].[LookupRuleSet] = 'Motor'
AND [WL].[LookupVariety] = 'Worksource'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
LEFT(RIGHT([L].[LookupStri