Here's an attempt at it. I'm getting an error msg on line 9 (Incorrect syntax near '>'. Can anyone tell me why I am getting the error msg? and If my syntax for the translation is correct?
;With ClientDomains_CTE3
AS
(
SELECT ClientDomains.DomainType
, ClientDomains.ClientVndNbr
, ClientDomains.ClientVndName
, Case ClientVndName
WHEN PATINDEX('% CO%', ClientVndName) > 0
AND SUBSTRING([ClientVndName], PATINDEX('% CO%',[ClientVndName]),4) = ' CO '
THEN PATINDEX('% CO%', ClientVndName)
WHEN PATINDEX('% INC%', ClientVndName) > 0
THEN PATINDEX('% INC%', ClientVndName)
WHEN PATINDEX('% LLC%', ClientVndName) > 0
THEN PATINDEX('% LLC%', ClientVndName)
ELSE 0
END AS EndPt
, LEFT(ClientVndName, CASE ClientVndName
WHEN PATINDEX('% CO%', ClientVndName) > 0
And SUBSTRING(ClientVndName, PATINDEX('% CO%', ClientVndName),4) = ' CO '
THEN PATINDEX('% CO%', ClientVndName)
WHEN PATINDEX('% INC%', ClientVndName) > 0
THEN PATINDEX('% INC%', ClientVndName)
WHEN PATINDEX('% LLC%', ClientVndName) > 0
THEN PATINDEX('% LLC%', ClientVndName)
ELSE 0
END) AS ShortName
FROM ClientDomains
WHERE ClientDomains.DomainType = 'CltDom'
AND (CASE ClientVndName
WHEN PATINDEX('% CO%', ClientVndName) > 0 AND SUBSTRING(ClientVndName, PATINDEX('% CO%', ClientVndName),4) = ' CO '
THEN PATINDEX('% CO%', ClientVndName)
WHEN PATINDEX('% INC%', ClientVndName) > 0
THEN PATINDEX('% INC%', ClientVndName)
WHEN PATINDEX('% LLC%', ClientVndName) > 0
THEN PATINDEX('% LLC%', ClientVndName)
) > 0
AND (ClientDomains.CorpVndNbr = 0 OR ClientDomains.CorpVndNbr IS NULL)
)
Select * From ClientDomains_CTE3
--Here's the orignal ms access vba code I tried translating:
SELECT Client_Domains.Type
, Client_Domains.Client_VndNbr
, Client_Domains.Client_VndName
, IIf(InStr(1,[Client_VndName],' CO')>0 And Mid([Client_VndName],InStr(1,[Client_VndName],' CO'),4)=' CO '
,InStr(1,[Client_VndName],' CO')
,IIf(InStr(1,[Client_VndName],' INC')>0
,InStr(1,[Client_VndName],' INC')
,IIf(InStr(1,[Client_VndName],' LLC')>0
,InStr(1,[Client_VndName],' LLC'),0)
)
) AS EndPt
, Left([Client_VndName]
,IIf(InStr(1,[Client_VndName],' CO')>0 And Mid([Client_VndName],InStr(1,[Client_VndName],' CO'),4)=' CO '
,InStr(1,[Client_VndName],' CO')
,IIf(InStr(1,[Client_VndName],' INC')>0
,InStr(1,[Client_VndName],' INC')
,IIf(InStr(1,[Client_VndName],' LLC')>0
,InStr(1,[Client_VndName],' LLC'),0)
)
)
-1) AS ShortName
FROM Client_Domains
WHERE Client_Domains.Type = "CltDom"
AND
(IIf(InStr(1,[Client_VndName],' CO')>0 And Mid([Client_VndName],InStr(1,[Client_VndName],' CO'),4)=' CO '
,InStr(1,[Client_VndName],' CO')
,IIf(InStr(1,[Client_VndName],' INC')>0
,InStr(1,[Client_VndName],' INC')
,IIf(InStr(1,[Client_VndName],' LLC')>0
,InStr(1,[Client_VndName],' LLC'),0)
)
)
) > 0
AND ((Client_Domains.CorpVndNbr)=0 Or (Client_Domains.CorpVndNbr) Is Null)