mysql: how to use case value in where clause

[the_nerd]

pls. don't laugh ..

I'd like to use CASE in a mySQL query:

SELECT
CASE WHEN id < 1000
THEN "small"
ELSE "big"
END AS size
FROM `mytab`

No problem so far.

But now, I'd like to use the value of "size" in the where clause:

SELECT ...
.....
WHERE size = "big"

But get an error #1054 - Unknown column 'size' in 'where clause'

IS there no way to use calculated results in a where clause? (I tried MS SQL SERVER as well, also negative)

The real query is about 100 lines long and duplicating all the logic in the where clause would easily double its size.

thanks, nerd

[txbakers]

Well, your logic indicated that "big" is < 1000, so why not just use that in the where instead of using the case.

[the_nerd]

Well, your logic indicated that "big" is < 1000, so why not just use that in the where instead of using the case.

Because there are (1) more than one of these values, (2) every value (basically an error condition) is calculated from up to 10 other values and (3) I want to get all records that have some kind of error ( error1 OR error 2, ...) - and in the php program reading the data I want to know the error condition without having to go back to the details.

SELECT
CASE ....... as error1,
CASE ...... as error2,
CASE ..... as error 3,
...
CASE .... as error n

FROM .....

WHERE error1 or error2 or .... or errorn

If I cannot reference the values of errorN in the WHERE clause, all I can do is either

- return all records to php and then throw away all that have no errors (dumb, if there's 2 million records but only 4 errors) or

- repeat the conditions for error1 .. errorn in the WHERE clause.

[txbakers]

wouldn't this work:

SELECT 'big' as size
FROM 'mytab'
WHERE id >=1000

That gives you the same results as your case query above.

[the_nerd]

yes, but this doesn't work:

SELECT
CASE WHEN a = 1 OR b = 7 OR C = 9 THEN 1 ELSE 0 END as error1,
CASE WHEN x = 1 AND (y = 7 OR z = 9) THEN 1 ELSE 0 END as error2,

FROM ...
WHERE
(a=1 OR b = 7 OR C = 9)
OR
(x = 1 AND (y = 7 OR z = 9))

Of course I CAN write it this way, but I just don't see a reason why I shouldn't be allowed to use the value of "error1" and "error2" in the WHERE clause .

[txbakers]

I'm not sure why it doesn't work. I've had difficulty using an aliased field name in the Having clause too.

[emsaw]

Maybe this will work:

SELECT
size = CASE WHEN id < 1000
THEN "small"
ELSE "big"
END FROM `mytab`
WHERE size = "big"

I have something very similar working for a complex query.. but I'm actually just using the case statement and that calculated value for an ORDER BY
.

HTH,

Mark

[syber]

Try using a derived table (it is ANSI SQL so it should work)

SELECT * FROM  
(SELECT  
 CASE WHEN id < 1000  
 THEN "small"  
 ELSE "big"  
 END AS size  
 FROM `mytab`) AS T1 
WHERE size = "big" 

[mukhtar2t]

ok this will work:
select your_fields from
your_table
where $your_error_value = case when your_condition_1 then 'value_1' when your_condition_2 then 'value_2' ... when your_condition_n then 'value_n' else 'value_else' end
for example
to show the errors types
select case when (id=1 or id=3) then 'error1' when (id in (4,7)) then 'error2' when id>1000 then 'error3' else 'unknown error' end from errors_table

to show the records that match an error type
select * from errors_table where 'error1' = case when (id=1 or id=3) then 'error1' when (id in (4,7)) then 'error2' when id>1000 then 'error3' else 'unknown error' end

that is ok.
best regards

[the_nerd]

syber,

Try using a derived table (it is ANSI SQL so it should work)

thanks for your idea. Problem is: I want it to be compatible with MySQL 4.0 and inner queries seem to be allowed only from 4.1 on.

nerd.

[the_nerd]

mukhtar2t,

pls. correct me if I'm wrong (should be sleeping an hour after midnight...)

Your first example brings up the error codes - for all records, even if there's no error in a record

The second query brings up the records that have errors, but doesn't show the error codes.

I want one query, showing all the records that are valid under one of the conditions - and bringing back the error codes. And I want it without retyping the condition in SELECT and WHERE. Why? Because
1. I don't want to enter identical information twice
2. I want to avoid errors when changing the query

Again, thanks for all the suggestions. So far the "inner query" seems to be the best idea, but it still adds some overhead. (code and processing time?)

nerd.

[syber]

Did you try it?

[mukhtar2t]

ok this is what you want:
select id,desciption,case when (id=1 or id=3) then 'error1' when (id in (4,7)) then 'error2' when id>1000 then 'error3' else 'unknown error' end as error_type from errors_table having error_type = 'error3'

[the_nerd]

And the oscar goes to ....

mukhtar2t, because this one works even with MySQL 4.0x (I always thought "having" can only be used with group by?)

SELECT ftextid,
CASE WHEN ftextid >17000
THEN 'big'
ELSE 'small'
END AS size
FROM sbk__cmsftext1
HAVING size = 'big'

and: syber

SELECT ftextid, size
FROM (

SELECT ftextid,
CASE WHEN ftextid >17000
THEN 'big'
ELSE 'small'
END AS size
FROM sbk__cmsftext1
) AS tmptab
WHERE size = 'big'

Thanks again,

nerd.