Here is the JOIN with CASE statements that I used:
LEFT OUTER JOIN
test.dbo.aVend on ((SUBSTRING(test.dbo.aVend.Description, 1, 8) = SUBSTRING(dbo.SubOrganization.Organization, 1, 8))
and
(CASE
WHEN len(test.dbo.aVend.Description) > 18 and ( SUBSTRING(test.dbo.aVend.Description, 1, 18) = SUBSTRING(dbo.SubOrganization.Organization, 1, 18)) THEN 1
WHEN len(test.dbo.aVend.Description) > 18 and ( SUBSTRING(test.dbo.aVend.Description, 1, 18) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 18)) THEN 0
else 1
END = 1)
and
(CASE
WHEN len(test.dbo.aVend.Description) > 14 and ( SUBSTRING(test.dbo.aVend.Description, 1, 14) = SUBSTRING(dbo.SubOrganization.Organization, 1, 14)) THEN 1
WHEN len(test.dbo.aVend.Description) > 14 and ( SUBSTRING(test.dbo.aVend.Description, 1, 14) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 14)) THEN 0
END = 1)
and
(CASE
WHEN len(test.dbo.aVend.Description) > 12 and ( SUBSTRING(test.dbo.aVend.Description, 1, 12) = SUBSTRING(dbo.SubOrganization.Organization, 1, 12)) THEN 1
WHEN len(test.dbo.aVend.Description) > 12 and ( SUBSTRING(test.dbo.aVend.Description, 1, 12) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 12)) THEN 0
END = 1)
and
(CASE
WHEN len(test.dbo.aVend.Description) > 11 and ( SUBSTRING(test.dbo.aVend.Description, 1, 11) = SUBSTRING(dbo.SubOrganization.Organization, 1, 11)) THEN 1
WHEN len(test.dbo.aVend.Description) > 11 and ( SUBSTRING(test.dbo.aVend.Description, 1, 11) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 11)) THEN 0
END = 1)
)
where test.dbo.aVend.[Comp ID] not in (501255, 501221, 500342) or test.dbo.aVend.[Comp ID] is null
No comments:
Post a Comment
Thank you for commenting!