What is wrong with this statement

Question

Post reply

What is wrong with this statement

ramadesai108

SSChampion

Points: 10609
More actions
March 13, 2009 at 6:31 am

#128573

Hi All,
What is wrong with this statement. Even when CHARINDEX is zero it goes thru the 2nd case statement:
CASE WHEN CHARINDEX('-',ClientID) <> 0 THEN
(CASE WHEN ISNUMERIC(SUBSTRING(ClientID,CHARINDEX('-',ClientID) + 1, 5)) = 1
THEN CONVERT(INT,SUBSTRING(ClientID,CHARINDEX('-',vchClientSubSessionID) + 1, 5))
ELSE ClientID END)
ELSE ClientID END
Thanks.

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 1

Can you confirm what datatypes 'ClientID' and 'vchClientSubSessionID' are.

Also some example values of ClientID

Kev

Dugi SSCoach Points: 17998 More actions · Answer 2

ramadesai108 (3/13/2009)
Hi All,
What is wrong with this statement. Even when CHARINDEX is zero it goes thru the 2nd case statement:
CASE WHEN CHARINDEX('-',ClientID) <> 0 THEN
(CASE WHEN ISNUMERIC(SUBSTRING(ClientID,CHARINDEX('-',ClientID) + 1, 5)) = 1
THEN CONVERT(INT,SUBSTRING(ClientID,CHARINDEX('-',vchClientSubSessionID) + 1, 5))
ELSE ClientID END)
ELSE ClientID END
Thanks.

CASE ClientID

WHEN CHARINDEX('-',ClientID) <> 0 THEN

(CASE WHEN ISNUMERIC(SUBSTRING(ClientID,CHARINDEX('-',ClientID) + 1, 5)) = 1

THEN CONVERT(INT,SUBSTRING(ClientID,CHARINDEX('-',vchClientSubSessionID) + 1, 5))

ELSE ClientID END)

ELSE ClientID END

this should work ...changes are with Italics

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 3

Explaining what you're trying to achieve sounds like a good idea... Along with DDL and sample input. Since I don't know what you're trying to do, the only thing that seems strange to me is the vchClientSubSessionID item. When I change that to ClientID it looks a totally reasonable query.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

Dugi SSCoach Points: 17998 More actions · Answer 4

kevriley (3/13/2009)
Can you confirm what datatypes 'ClientID' and 'vchClientSubSessionID' are.
Also some example values of ClientID
Kev

Ooops kevriley is correct!!!

Post more explanation ... see my signature with online link how can we help you properly!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 5

Why are you using the vchClientSubSessionID column in the CONVERT expression when you used ClientID in the CASE WHEN boolean expressions?

Also ISNUMERIC isn't the best way to validate an integer as it returns 1 for certain strings that don't convert to an integer

e.g.

SELECT ISNUMERIC('2E2') /* returns 1 */

SELECT CONVERT(int, '2E2') /* fails */

As an alternative, you could use something like:

CASE WHEN RTRIM(SUBSTRING(ClientID, CHARINDEX('-', ClientID) + 1, 5)) NOT LIKE '%[^0-9]%' THEN...

ramadesai108 SSChampion Points: 10609 More actions · Answer 6

ok here is my updated code:

CASE ClientID WHEN CHARINDEX('-',ClientID) <> 0 THEN

(CASE WHEN ISNUMERIC(SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5)) = 1

THEN CONVERT(INT,SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5))

ELSE ClientID END)

ELSE ClientID END

it gives me "Incorrect syntax near '<'."

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

CASE has two syntaxes

CASE < column or variable > WHEN < value > THEN ...

or

CASE WHEN < Boolean expression > THEN ....

They are not mixable. Change it to

CASE WHEN CHARINDEX('-',ClientID) <> 0

THEN CASE WHEN ISNUMERIC(SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5)) = 1

THEN CONVERT(INT,SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5))

ELSE ClientID END

and the syntax error will go away.

As for the logic problems, can you post some sample data along with your expected results for each value of ClientID?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 8

ramadesai108 (3/13/2009)
ok here is my updated code:
CASE ClientID WHEN CHARINDEX('-',ClientID) <> 0 THEN
(CASE WHEN ISNUMERIC(SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5)) = 1
THEN CONVERT(INT,SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5))
ELSE ClientID END)
ELSE ClientID END
it gives me "Incorrect syntax near '<'."

Don't change your code to be like this, it was syntactically correct to start with.

This 'new' version is mixing up the 2 ways to use a CASE statement (simple and searched) see http://msdn.microsoft.com/en-us/library/ms181765(SQL.90).aspx

Please provide sample data otherwise it's virtualy impossible to undertsand your issue.

Kev

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 9

Kev Riley

SSCrazy Eights

Points: 9075

March 13, 2009 at 8:54 am

#959179

Gail beat me to it!

Kev

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Dugi (3/13/2009)
CASE ClientID
WHEN CHARINDEX('-',ClientID) <> 0 THEN
(CASE WHEN ISNUMERIC(SUBSTRING(ClientID,CHARINDEX('-',ClientID) + 1, 5)) = 1
THEN CONVERT(INT,SUBSTRING(ClientID,CHARINDEX('-',vchClientSubSessionID) + 1, 5))
ELSE ClientID END)
ELSE ClientID END
this should work ...changes are with Italics

Did you test that before recommending it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mister boom Say Hey Kid Points: 663 More actions · Answer 11

Hi

I think the answer is that when SQL Server parses your query, it checks that the output of your CASE statement will always be of a consistent data type.

With your query, you are checking if the 5 characters after the '-' are numeric, and if so, you use CONVERT to force an INT output. However, the fact that you are checking whether those 5 characters in ClientID are numeric suggests that ClientID isn't numeric. i.e. your query is saying:

If these 5 characters are numeric, return an integer,

otherwise return a string.

Sorry, but I don't think that's possible.

Al

ramadesai108 SSChampion Points: 10609 More actions · Answer 12

Thanks for the response. Here is my proc: The case statement is in order by clause. How can I make it more efficient:

CREATE PROCEDURE [dbo].[sp_getClients]

AS

BEGIN

SELECT intID, ClientID

FROM Clients

WHERE intID = @intID

ORDER BY intID,

CASE WHEN CHARINDEX('-',ClientID) <> 0 THEN

(CASE WHEN ISNUMERIC(SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5)) = 1

THEN CONVERT(INT,SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5))

ELSE ClientID END)

ELSE ClientID END

END

GO

thanks.

ramadesai108 SSChampion Points: 10609 More actions · Answer 13

If I run the proc with param 'Sp1' then i get the following:

Conversion failed when converting the nvarchar value 'Sp1' to data type int.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Do you have an appropriate index?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass