March 28, 2008 at 6:31 am
Hi, I need to produce an extract, part of the code of which I need help with.
I want to only extract data as [CUST_ROLE] where the three statements are satisified ie I dont want any other data from the table returned and therefore dont want to use an 'else' statement.
For example, the first statement run alone as a normal select would return 100 rows, the second would return 200 rows and the third would return 50 rows. Therefore I'd expect a total return of 350. However when I run it using case as below I get all rows returned from the table ie 1000, whether the source_channel is 3 or not!!! When using case do you always get back all rows from the table. Should I be using some sort of IF/THEN statement instead. Any advice & examples would be gratefully received...Thank-you
select
CASE
WHEN (h.source_channel = '3') and (h.primary_cust_ref like '%C%') THEN 'Guest'
WHEN (h.source_channel = '3') and (h.primary_cust_ref like '%H%') THEN 'Host'
WHEN (h.source_channel = '3') and (h.primary_cust_ref not like '%H%') and (h.primary_cust_ref not like '%C%') THEN 'Consultant'
END AS [CUST_ROLE]
from header h
March 28, 2008 at 6:39 am
If I understand what you are trying to return (which is only those rows with h.source_channel = '3') then you need to add a where clause as shown below and the h.source_channel = '3' in the when portion of your query would be redundant.
select
CASE
WHEN (h.primary_cust_ref like '%C%') THEN 'Guest'
WHEN (h.primary_cust_ref like '%H%') THEN 'Host'
WHEN (h.primary_cust_ref not like '%H%') and (h.primary_cust_ref not like '%C%') THEN 'Consultant'
END AS [CUST_ROLE]
from header h
where h.source_channel = '3'
CRC
March 28, 2008 at 7:18 am
Thanks for that CRC. Unfortunately like I say its part of a much larger piece of code which has other joins ands columns its retrieving and i cant limit it to just that source_channel for the whole query. However it has made me realise that what the requestor wants is not possible as the main query IS going to retrieve everything from the table, so this part of it cant be limited to just that source channel. I think I probably do need an ELSE statement for those rows which aren't source channel 3.
Thank-you for your prompt response
March 28, 2008 at 7:18 am
Agreed - like CRC mentioned, CASE conditionally adjusts the row output, but it doesn't filter rows (prevent them from being displayed). Add a WHERE clause, and use that to determine what rows should be returned.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 28, 2008 at 2:35 pm
select
CASE
WHEN (h.source_channel = '3') and (h.primary_cust_ref like '%C%') THEN 'Guest'
WHEN (h.source_channel = '3') and (h.primary_cust_ref like '%H%') THEN 'Host'
WHEN (h.source_channel = '3') and (h.primary_cust_ref not like '%H%') and (h.primary_cust_ref not like '%C%') THEN 'Consultant'
ELSE 'UNKNOWN'
END AS [CUST_ROLE]
from header h
where
CASE
WHEN (h.source_channel = '3') and (h.primary_cust_ref like '%C%') THEN 'Guest'
WHEN (h.source_channel = '3') and (h.primary_cust_ref like '%H%') THEN 'Host'
WHEN (h.source_channel = '3') and (h.primary_cust_ref not like '%H%') and (h.primary_cust_ref not like '%C%') THEN 'Consultant'
ELSE 'UNKNOWN'
END <> 'UNKNOWN'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply