On Friday 29 February 2008 2:01 pm, Ed Leafe wrote:
> 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 ;
The other way to do it is (not tested):
select alias.fld1, alias.fld2,alias.fld3 from (select *
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...) as alias
having alias.alphanum < 10
>
> This worked great, and is essentially what a having clause is
> supposed to do in the first place.
>
>
> -- Ed Leafe
>
>
>
>
>
[excessive quoting removed by server]
_______________________________________________
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]
