Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert IIF to Case Statements and Instr to charindex or Like Expand / Collapse
Author
Message
Posted Thursday, March 6, 2014 3:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:43 AM
Points: 159, Visits: 422
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));



Post #1548516
Posted Friday, March 7, 2014 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:43 AM
Points: 159, Visits: 422
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)



Post #1548740
Posted Friday, March 7, 2014 8:05 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:52 AM
Points: 449, Visits: 1,370
Can you provide some sample data and expected output for this please? In the middle of giving it a go now :)
Post #1548752
Posted Friday, March 7, 2014 8:27 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:52 AM
Points: 449, Visits: 1,370
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_VndName EndPt ShortName
Test 1 CO Lala 7 Test 1
Test 2 CO Lala 7 Test 2
Test 5 INC Lala 7 Test 5
Post #1548760
Posted Friday, March 7, 2014 8:46 AM This worked for the OP Answer marked as solution


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:52 AM
Points: 449, Visits: 1,370
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
)

Post #1548768
Posted Friday, March 7, 2014 9:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:43 AM
Points: 159, Visits: 422
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



Post #1548795
Posted Friday, March 7, 2014 10:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:43 AM
Points: 159, Visits: 422
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



Post #1548810
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse