Using CASE and LIKE (Do I need to break into 2 different Queries)

Question

Using CASE and LIKE (Do I need to break into 2 different Queries)

Jacob Pressures

SSCertifiable

Points: 5804
More actions
May 13, 2009 at 10:43 am

#134187

Do I have to break this query into two different queries? It works perfectly fine when i break it apart. But it just seems strange to have to break apart a query that is so similar in nature and function. My query currently looks like this:
SELECT EmergencyRelation , CASE EmergencyRelation
WHEN 'Aunt' THEN 'A'
WHEN 'Uncle' THEN 'U'
WHEN 'Brother' THEN 'B'
WHEN 'Sister' THEN 'S'
WHEN 'Mother' THEN 'M'
WHEN 'Mom' THEN 'M'
WHEN 'Father' THEN 'F'
WHEN 'Grandmother' THEN 'GM'
WHEN 'Grandfather' THEN 'GF'
WHEN 'Neighbor' THEN 'N'
WHEN 'Friend' THEN 'FR'
WHEN 'Cousin' THEN 'C'
WHEN 'Caretaker' THEN 'CT'
WHEN 'Babysitter' THEN 'BS'
WHEN 'Godfather' THEN 'Gfa'
WHEN 'Godmother' THEN 'Gmo'
WHEN 'Niece' THEN 'Nie'
WHEN 'Pastor' THEN 'Pa'
WHEN 'Great Aunt' THEN 'A'
WHEN (EmergencyRelation LIKE 'Godp%') THEN 'Gmo'
WHEN (EmergencyRelation LIKE 'Grandp%') THEN 'GM'
WHEN (EmergencyRelation LIKE 'Aunt%') THEN 'A'
WHEN (EmergencyRelation IS NULL) THEN 'NA' END
FROM Student
This works:
SELECT EmergencyRelation , CASE EmergencyRelation
WHEN 'Aunt' THEN 'A'
WHEN 'Uncle' THEN 'U'
WHEN 'Brother' THEN 'B'
WHEN 'Sister' THEN 'S'
WHEN 'Mother' THEN 'M'
WHEN 'Mom' THEN 'M'
WHEN 'Father' THEN 'F'
WHEN 'Grandmother' THEN 'GM'
WHEN 'Grandfather' THEN 'GF'
WHEN 'Neighbor' THEN 'N'
WHEN 'Friend' THEN 'FR'
WHEN 'Cousin' THEN 'C'
WHEN 'Caretaker' THEN 'CT'
WHEN 'Babysitter' THEN 'BS'
WHEN 'Godfather' THEN 'Gfa'
WHEN 'Godmother' THEN 'Gmo'
WHEN 'Niece' THEN 'Nie'
WHEN 'Pastor' THEN 'Pa'
WHEN 'Great Aunt' THEN 'A' END
FROM Student
SELECT EmergencyRelation , CASE
WHEN (EmergencyRelation LIKE 'Godp%') THEN 'Gmo'
WHEN (EmergencyRelation LIKE 'Grandp%') THEN 'GM'
WHEN (EmergencyRelation LIKE 'Aunt%') THEN 'A'
WHEN (EmergencyRelation IS NULL) THEN 'NA' END
FROM Student
Is breaking it apart the only way to make it work? Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Jacob Pressures (5/13/2009)
Do I have to break this query into two different queries? It works perfectly fine when i break it apart. But it just seems strange to have to break apart a query that is so similar in nature and function. My query currently looks like this:
SELECT EmergencyRelation , CASE EmergencyRelation
WHEN 'Aunt' THEN 'A'
WHEN 'Uncle' THEN 'U'
WHEN 'Brother' THEN 'B'
WHEN 'Sister' THEN 'S'
WHEN 'Mother' THEN 'M'
WHEN 'Mom' THEN 'M'
WHEN 'Father' THEN 'F'
WHEN 'Grandmother' THEN 'GM'
WHEN 'Grandfather' THEN 'GF'
WHEN 'Neighbor' THEN 'N'
WHEN 'Friend' THEN 'FR'
WHEN 'Cousin' THEN 'C'
WHEN 'Caretaker' THEN 'CT'
WHEN 'Babysitter' THEN 'BS'
WHEN 'Godfather' THEN 'Gfa'
WHEN 'Godmother' THEN 'Gmo'
WHEN 'Niece' THEN 'Nie'
WHEN 'Pastor' THEN 'Pa'
WHEN 'Great Aunt' THEN 'A'
WHEN (EmergencyRelation LIKE 'Godp%') THEN 'Gmo'
WHEN (EmergencyRelation LIKE 'Grandp%') THEN 'GM'
WHEN (EmergencyRelation LIKE 'Aunt%') THEN 'A'
WHEN (EmergencyRelation IS NULL) THEN 'NA' END
FROM Student
This works:
SELECT EmergencyRelation , CASE EmergencyRelation
WHEN 'Aunt' THEN 'A'
WHEN 'Uncle' THEN 'U'
WHEN 'Brother' THEN 'B'
WHEN 'Sister' THEN 'S'
WHEN 'Mother' THEN 'M'
WHEN 'Mom' THEN 'M'
WHEN 'Father' THEN 'F'
WHEN 'Grandmother' THEN 'GM'
WHEN 'Grandfather' THEN 'GF'
WHEN 'Neighbor' THEN 'N'
WHEN 'Friend' THEN 'FR'
WHEN 'Cousin' THEN 'C'
WHEN 'Caretaker' THEN 'CT'
WHEN 'Babysitter' THEN 'BS'
WHEN 'Godfather' THEN 'Gfa'
WHEN 'Godmother' THEN 'Gmo'
WHEN 'Niece' THEN 'Nie'
WHEN 'Pastor' THEN 'Pa'
WHEN 'Great Aunt' THEN 'A' END
FROM Student
SELECT EmergencyRelation , CASE
WHEN (EmergencyRelation LIKE 'Godp%') THEN 'Gmo'
WHEN (EmergencyRelation LIKE 'Grandp%') THEN 'GM'
WHEN (EmergencyRelation LIKE 'Aunt%') THEN 'A'
WHEN (EmergencyRelation IS NULL) THEN 'NA' END
FROM Student
Is breaking it apart the only way to make it work? Thanks!

You are trying to mix the two different forms of CASE statement in the the first select statement. If you change the CASE to this, it should work:

CASE

WHEN EmergencyRelation = 'Aunt' THEN 'A'

WHEN EmergencyRelation = 'Uncle' THEN 'U'

WHEN EmergencyRelation = 'Brother' THEN 'B'

WHEN EmergencyRelation = 'Sister' THEN 'S'

WHEN EmergencyRelation = 'Mother' THEN 'M'

WHEN EmergencyRelation = 'Mom' THEN 'M'

WHEN EmergencyRelation = 'Father' THEN 'F'

WHEN EmergencyRelation = 'Grandmother' THEN 'GM'

WHEN EmergencyRelation = 'Grandfather' THEN 'GF'

WHEN EmergencyRelation = 'Neighbor' THEN 'N'

WHEN EmergencyRelation = 'Friend' THEN 'FR'

WHEN EmergencyRelation = 'Cousin' THEN 'C'

WHEN EmergencyRelation = 'Caretaker' THEN 'CT'

WHEN EmergencyRelation = 'Babysitter' THEN 'BS'

WHEN EmergencyRelation = 'Godfather' THEN 'Gfa'

WHEN EmergencyRelation = 'Godmother' THEN 'Gmo'

WHEN EmergencyRelation = 'Niece' THEN 'Nie'

WHEN EmergencyRelation = 'Pastor' THEN 'Pa'

WHEN EmergencyRelation = 'Great Aunt' THEN 'A'

WHEN (EmergencyRelation LIKE 'Godp%') THEN 'Gmo'

WHEN (EmergencyRelation LIKE 'Grandp%') THEN 'GM'

WHEN (EmergencyRelation LIKE 'Aunt%') THEN 'A'

WHEN (EmergencyRelation IS NULL) THEN 'NA'

ELSE 'NA' END -- Added ELSE just in case

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 2

Sorry, I thought I had responded before leaving! Thanks that was perfect!!! And quick too!

How did you get the colors to show up? Thanks again.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Glad I was able to assist. As for the syntax highlighting, I used the IFCodes [ code ][ /code ] (no spaces inside the brackets).

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 4

Thanks. I thought about using that but I wasn't sure what it was so i left it alone.