March 6, 2014 at 3:32 pm
I want to convert the ms access sql statement below to a Case statement in sql server TSQL. There are IIF statements that need to be Case statements and InStr statements that need to be converted to Like or charindex? I'm not exactly sure how the syntax for the charindexes should be, but I think I can handle the Case statement. Can anyone help with this?
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));
March 7, 2014 at 7:29 am
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)
March 7, 2014 at 8:05 am
Can you provide some sample data and expected output for this please? In the middle of giving it a go now 🙂
March 7, 2014 at 8:27 am
In the absence of test data here is my spin on it
DECLARE @Client_Domains TABLE
(
Client_VndName NVARCHAR(100) ,
Client_Type NVARCHAR(100) ,
CorpVndNbr INT
)
INSERT INTO @Client_Domains
( Client_VndName, Client_Type, CorpVndNbr )
VALUES ( N'Test 1 CO Lala', 'CltDom', 0 ),
( N'Test 2 CO Lala', 'CltDom', NULL ),
( N'Test 3 CO Lala', 'ABCDom', 1 ),
( N'Test 4 CO Lala', 'ABCDom', NULL ),
( N'Test 5 INC Lala', 'CltDom', NULL ),
( N'Test 6 Lala', 'CltDom', 1 ),
( N'Test 7 Lala', 'CltDom', NULL )
SELECT Client_VndName ,
CASE WHEN CHARINDEX(' CO', Client_VndName) > 0
AND SUBSTRING(Client_VndName,
CHARINDEX(' CO', Client_VndName), 4) = ' CO '
THEN CHARINDEX(' CO', Client_VndName)
WHEN CHARINDEX(' INC', Client_VndName) > 0
THEN CHARINDEX(' INC', Client_VndName)
WHEN CHARINDEX(' LLC', Client_VndName) > 0
THEN CHARINDEX(' LLC', Client_VndName)
ELSE 0
END AS EndPt ,
SUBSTRING(Client_VndName, 1,
CASE WHEN CHARINDEX(' CO', Client_VndName) > 0
AND SUBSTRING(Client_VndName,
CHARINDEX(' CO', Client_VndName), 4) = ' CO '
THEN CHARINDEX(' CO', Client_VndName)
WHEN CHARINDEX(' INC', Client_VndName) > 0
THEN CHARINDEX(' INC', Client_VndName)
WHEN CHARINDEX(' LLC', Client_VndName) > 0
THEN CHARINDEX(' LLC', Client_VndName)
ELSE 0
END) AS ShortName
FROM @Client_Domains
WHERE ( Client_Type = 'CltDom' )
AND ( CorpVndNbr = 0
OR CorpVndNbr IS NULL
)
AND CASE WHEN CHARINDEX(' CO', Client_VndName) > 0
AND SUBSTRING(Client_VndName,
CHARINDEX(' CO', Client_VndName), 4) = ' CO '
THEN 1
WHEN CHARINDEX(' INC', Client_VndName) > 0 THEN 1
WHEN CHARINDEX(' LLC', Client_VndName) > 0 THEN 1
ELSE 0
END = 1
And the final output
Client_VndNameEndPtShortName
Test 1 CO Lala7Test 1
Test 2 CO Lala7Test 2
Test 5 INC Lala7Test 5
March 7, 2014 at 8:46 am
Actually this way seems to work also
DECLARE @Client_Domains TABLE
(
Client_VndName NVARCHAR(100) ,
Client_Type NVARCHAR(100) ,
CorpVndNbr INT
)
INSERT INTO @Client_Domains
( Client_VndName, Client_Type, CorpVndNbr )
VALUES ( N'Test 1 CO Lala', 'CltDom', 0 ),
( N'Test 2 CO Lala', 'CltDom', NULL ),
( N'Test 3 CO Lala', 'ABCDom', 1 ),
( N'Test 4 CO Lala', 'ABCDom', NULL ),
( N'Test 5 INC Lala', 'CltDom', NULL ),
( N'Test 6 Lala', 'CltDom', 1 ),
( N'Test 7 Lala', 'CltDom', NULL )
SELECT * ,
SUBSTRING(Client_VndName, 1,
CASE WHEN CHARINDEX(' CO', Client_VndName) > 0
AND SUBSTRING(Client_VndName,
CHARINDEX(' CO', Client_VndName), 4) = ' CO '
THEN CHARINDEX(' CO', Client_VndName)
WHEN CHARINDEX(' INC', Client_VndName) > 0
THEN CHARINDEX(' INC', Client_VndName)
WHEN CHARINDEX(' LLC', Client_VndName) > 0
THEN CHARINDEX(' LLC', Client_VndName)
ELSE 0
END) AS ShortName
FROM @Client_Domains
WHERE ( Client_VndName LIKE '% CO %'
OR Client_VndName LIKE '% INC %'
)
AND ( Client_Type = 'CltDom' )
AND ( CorpVndNbr = 0
OR CorpVndNbr IS NULL
)
March 7, 2014 at 9:50 am
Thanks for the reply with good code. Sorry I took long getting back but I went back to the business person and asked for these requirements in the original query and I think I simplified it much more. I am getting an erro msg on "as ShortName on Line 13. But I think this will give me what i want.
Data:
clientvndname(fieldname)
SPECIALITES PRODAL 1975
ABUNDANCE MARKETING INC
ACH FOOD COMPANIES C/O 910040
ACTION LIGHTING
AON REED STENHOUSE INC
LEAHY ORCHARDS INC.
AQPP
CANADIAN SPRINGS
ARLA FOODS INC
HILLMANS TRANSFER CO LTD
Results desired:
SPECIALITES PRODAL 1975
ABUNDANCE MARKETING
ACH FOOD COMPANIES C/O 910040
ACTION LIGHTING
AON REED STENHOUSE
LEAHY ORCHARDS
AQPP
CANADIAN SPRINGS
ARLA FOODS
HILLMANS TRANSFER
;With ClientDomains_CTE3
AS
(
SELECT ClientDomains.DomainType
, ClientDomains.ClientVndNbr
, ClientDomains.ClientVndName
, LEFT(ClientVndName
, RTRIM(REPLACE(
REPLACE(
REPLACE(
REPLACE(ClientVndName, ' CO ', ' ')
,' INC ', ' ')
,' INC.', ' ')
,' LTD ', ' ')) as ShortName
FROM ClientDomains
WHERE ClientDomains.DomainType = 'CltDom'
AND PATINDEX('% INC %',DomainName) > 0 OR PATINDEX('% CO %',DomainName) > 0 OR PATINDEX('% LTD %',DomainName) > 0 OR PATINDEX('% CO.',DomainName) > 0
AND (ClientDomains.CorpVndNbr = 0 OR ClientDomains.CorpVndNbr IS NULL)
)
Select * From ClientDomains_CTE3
March 7, 2014 at 10:25 am
found my error, this works. Thanks! higgim, your code gave me the structure I needed for my query below.
;With ClientDomains_CTE3
AS
(
SELECT ClientDomains.DomainType
, ClientDomains.ClientVndNbr
, ClientDomains.ClientVndName
, RTRIM(REPLACE(
REPLACE(
REPLACE(
REPLACE(ClientVndName, ' CO ', ' ')
,' INC ', ' ')
,' INC.', ' ')
,' LTD ', ' ')
) as ShortName
FROM ClientDomains
WHERE ClientDomains.DomainType = 'CltDom'
AND (ClientVndName LIKE '% INC %' OR ClientVndName LIKE '% CO %' OR ClientVndName LIKE '% INC.%' OR ClientVndName LIKE '% LTD %')
AND (ClientDomains.CorpVndNbr = 0 OR ClientDomains.CorpVndNbr IS NULL)
)
Select * From ClientDomains_CTE3
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply