Nested case statement not working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nested case statement not working

Author

Topic

Claire_Toronto
Starting Member

11 Posts

Posted - 2011-08-08 : 15:01:10

HI,

Could someone help fix this nested case statement so it will run?

Thanks

, CASE
when PATINDEX('%CRRW'%', FIELD_NAME) = 0 THEN
NULL
ELSE
SUBSTRING(filename, PATINDEX('%CRRW'%',
FIELD_NAME), 4)

(
case
when PATINDEX('%Dx_Bx%', FIELD_NAME) = 0 THEN
NULL
ELSE
SUBSTRING(FIELD_NAME, PATINDEX('%Dx_Bx%',
FIELD_NAME), 5)
)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-08 : 15:26:04

Since you din't give us any information about you issue, I'll take a guess

CASE
    WHEN PATINDEX('%CRRW' + CHAR(39) + '%', FIELD_NAME) = 0 
        THEN NULL
    ELSE
        SUBSTRING(filename, PATINDEX('%CRRW' + CHAR(39) + '%', FIELD_NAME), 4)
   END
,
case
    when PATINDEX('%Dx_Bx%', FIELD_NAME) = 0 
    THEN 
        NULL
    ELSE
        SUBSTRING(FIELD_NAME, PATINDEX('%Dx_Bx%',FIELD_NAME), 5)
    END

Claire_Toronto
Starting Member

11 Posts

Posted - 2011-08-08 : 15:37:40

yes please... my bad I forgot to give an intro lol

I have 50K rows which consist of different types of strings... I need a case statement which will search for the string specified and input it in a new column calling it exactly the name in the search...

the first case statement works fine... the only problem is I have 6-7 different criteria to find...

So it searches for CRRW and Dx_Bx etc and when it finds it, it will input it in a new column called TYPE if it doesn't find it it will call it NULL but still output NULL in the TYPE column...

Please let me know if I explained it clearly... thanks so much!

,CASE
when PATINDEX('%CRRW'%', FIELD_NAME) = 0 THEN
NULL
ELSE
SUBSTRING(filename, PATINDEX('%CRRW'%',
FIELD_NAME), 4)

(
case
when PATINDEX('%Dx_Bx%', FIELD_NAME) = 0 THEN
NULL
ELSE
SUBSTRING(FIELD_NAME, PATINDEX('%Dx_Bx%',
FIELD_NAME), 5)
)
END TYPE

ANSWER SET:

CRRW
Dx_Bx
NULL

etc depending on the string the output will be in a new column...

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-08-08 : 16:44:14

In that case you need to have 6-7 different case statements with AS COLUMN_NAME for each case...

quote:
Originally posted by Claire_Toronto

yes please... my bad I forgot to give an intro lol

I have 50K rows which consist of different types of strings... I need a case statement which will search for the string specified and input it in a new column calling it exactly the name in the search...

the first case statement works fine... the only problem is I have 6-7 different criteria to find...

So it searches for CRRW and Dx_Bx etc and when it finds it, it will input it in a new column called TYPE if it doesn't find it it will call it NULL but still output NULL in the TYPE column...

Please let me know if I explained it clearly... thanks so much!

,CASE
when PATINDEX('%CRRW'%', FIELD_NAME) = 0 THEN
NULL
ELSE
SUBSTRING(filename, PATINDEX('%CRRW'%',
FIELD_NAME), 4)

(
case
when PATINDEX('%Dx_Bx%', FIELD_NAME) = 0 THEN
NULL
ELSE
SUBSTRING(FIELD_NAME, PATINDEX('%Dx_Bx%',
FIELD_NAME), 5)
)
END TYPE

ANSWER SET:

CRRW
Dx_Bx
NULL

etc depending on the string the output will be in a new column...

ssdeveloper
Starting Member

37 Posts

Posted - 2011-08-10 : 17:11:28

Try this:

CASE
WHEN (table_name.column_name) LIKE '%CRRW%' THEN CRRW
WHEN (table_name.column_name) LIKE '%Dx_Bx%' THEN Dx_Bx
-- AND add all the patterns you have,as above
END TYPE

You should automatically get NULL if you dont have the string patterns mentioned above or you can use ELSE 'NULL' in the case statement if you like.

Hope this helps!

ssdeveloper
Starting Member

37 Posts

Posted - 2011-08-10 : 17:20:58

Sorry! I forgot the quotes around the string

CASE
WHEN (table_name.column_name) LIKE '%CRRW%' THEN 'CRRW'
WHEN (table_name.column_name) LIKE '%Dx_Bx%' THEN 'Dx_Bx'
-- AND add all the patterns you have,as above
END TYPE

Let me know if I understood your question properly.
Thanks

Claire_Toronto
Starting Member

11 Posts

Posted - 2011-08-11 : 22:14:03

Thank you so much!

works perfect!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources