Re: Basic sql
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: ChrisStephens@xxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 09 Nov 2004 22:24:57 +0100
Your use of case is rather unorthodox.
select count(*)
from a
where (op_year, unit_id, 1 + floor(extract(month from a.op_date)/4))
in (select op_year, unit_id, qtr
from b)
Avoid using EXISTS when you have no selective condition besides.
Stephens, Chris wrote:
>Well I've had my coffee and I still can't get this to work:
>
>SELECT COUNT(*) FROM a
> WHERE EXISTS (SELECT *=20
> FROM b
> WHERE a.unit_id =3D b.unit_id
> and a.op_year =3D b.op_year
> and extract(month from a.op_date) BETWEEN CASE
> WHEN b.qtr =3D 1 THEN 1 AND 3
> WHEN b.qtr =3D 2 THEN 4 AND 6
> WHEN b.qtr =3D 3 THEN7 AND 9
> WHEN b.qtr =3D 4 THEN 10 AND 12
> END)
>/
>
>I've tried several variations of this (all that I can think of)i.e.
>quotes and parenthesis in all kinds of places, case to build entire last
>filter instead of just the '1 and 3' pieces. and it always returns:
>
>ERROR at line 7:
>ORA-00905: missing keyword
>
>9.2 on windows
>
>What simple thing am I missing now?
>
>Thank you for the extra eyes!
>--
>//www.freelists.org/webpage/oracle-l
>
>
>
--
Regards,
Stephane Faroult
RoughSea Ltd
http://www.roughsea.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
