The CASE statement.

[creole]

I've read about this elusive beast but I've never been able to get it to work. There's hardly ANY information about it on the mySQL website so I wanted to have a thread here where someone knowledgable about database dev could give me a definitive answer on how and when to use it. Do you use it in the SELECT portion of your SQL? The WHERE? The ORDER BY? I can't beleive that something that could be so uesful is almost completely neglected on the mySQL website.

I'll start off by posting a query that I'm trying to get working with CASE.

SELECT p.prod_dateadded, CONCAT(m.mfr_name, ' ', p.prod_name) AS product, c.company_name, u.user_name
FROM tblmanufacturers m
INNER JOIN tblproducts p
    ON m.mfr_id = p.fk_mfr_id
LEFT OUTER JOIN tbluser u
    ON u.user_id = p.fk_user_id
LEFT OUTER JOIN tblcompany c
    ON c.company_id = u.fk_company_id
WHERE prod_dateadded BETWEEN '2004-11-01' AND '#DateFormat(Now(),"yyyy-mm-dd")#'
ORDER BY product 

At this point my query returns 3 rows. Two of those rows have 2 records each that return empty strings. I'd like to replace those empty strings with the letters "EMC". Obviously I could use CFIF statements to replace them but I really want to learn more SQL so I'm asking this question now.

Does anyone have any thoughts?

[r937]

Do you use it in the SELECT portion of your SQL?
yes

The WHERE?
yes

The ORDER BY?
yes

Two of those rows have 2 records each
????

I'd like to replace those empty strings with the letters "EMC".
CASE WHEN foo='' THEN 'EMC' ELSE foo END

[creole]

Sorry...each row returns 4 colums, two of which might be empty strings.

Rudy...you gave me an example of the CASE statement, but where do I put it? Do I SELECT it first or do I say WHERE CASE...?

[r937]

that depends on what you want, which i don't think i understand yet

[creole]

I see....

Using that query above, I get these results:
Date|Added|Product|Company|User Name
12-16-2004|10 Strawberry Street|ABC123|Hertz Rentals (test) Tim Quarrel
12-16-2004|10 Strawberry Street|Model One|Matthews Unit Management|George Bush
12-16-2004|Atlas Copco|XAS79||

You can see that in the last row, two of the results (company name and user name) are empty strings. So what I want to know how to do is to return the letters EMC in place of those empty strings in however many instances there are.

[creole]

Okay...

So I'm using this query now:

SELECT p.prod_dateadded, CONCAT(m.mfr_name, ' ', p.prod_name) AS product, u.user_name,
    CASE WHEN c.company_name = '' THEN 'EMC' ELSE c.company_name END AS company_name
FROM tblmanufacturers m
INNER JOIN tblproducts p
    ON m.mfr_id = p.fk_mfr_id
LEFT OUTER JOIN tbluser u
    ON u.user_id = p.fk_user_id
LEFT OUTER JOIN tblcompany c
    ON c.company_id = u.fk_company_id
WHERE prod_dateadded BETWEEN '2004-11-01' AND '#DateFormat(Now(),"yyyy-mm-dd")#'
ORDER BY product 

It doesn't really appear to change anything. The way I read this query is if company_name is empty (or ''), then use the text 'EMC', if not, then use company_name. It's not doing this though. Am I doing something wrong or leaving something out?

[r937]

empty is not the same as null

in a LEFT OUTER JOIN, you will get nulls in columns from right table(s) where the row doesn't have a match

instead of your CASE expression, try this --

select coalesce(company_name,'EMC') as company_name ...

[creole]

Rudy! That worked perfectly! Thank you!!

Just for future reference, is is possible to do the same thing with CASE? Another friend suggested I try the ISNULL function mixed in the CASE statement. Would that work too?

[r937]

yes, you can do the same with CASE, it's just wordier

i stay away from functions like ISNULL because i always have to look them up in the particular database system (mysql, sql server, etc.) to confirm that they does what i want them to do, and to make sure i'm not mixing up the syntax for ISNULL with NULLIF, etc.

COALESCE is standard and works the same in all databases