On Feb 21, 2008, at 7:19 PM, Adrian Klaver wrote:
> Here is better description of the semantics, followed by info from
> the MySQL
> docs for 5.0 that indicates that they use a different semantic model
> and do
> not follow the SQL standard except in ambiguous cases .
Thanks. I still find this whole thing frustrating.
Today I had a query like:
select tbl.fld1, tbl2.fld2, tbl3.fld3,
case
when x<10 then 1
when x<20 then 2
when x>50 then 3
...
when x=100 then 33
end as alphanum
from tbl join tbl2...
join tbl3...
having alphanum < 10
This of course did not work, due to the inability to use aliases in
the having clause. Obviously the actual statements in the 'case' were
not so trivial, and it ended up being about 30 lines. In order to
filter the final result set, I would have had had to repeat the entire
'case' structure for the having clause. That was just plain stupid, so
I wrote it like this:
create temporary table tmp as
[select statement from above, but
without the having]
select * from tmp
where alphanum < 10 ;
drop table tmp ;
This worked great, and is essentially what a having clause is
supposed to do in the first place.
-- Ed Leafe
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/dabo-users/[EMAIL PROTECTED]
