You can try this if you still want to use the case statement
SELECT COUNT(*) FROM a
WHERE EXISTS (SELECT *=3D20
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
WHEN b.qtr =3D 2 THEN 4=20
WHEN b.qtr =3D 3 THEN 7=20
WHEN b.qtr =3D 4 THEN 10=20
END
AND
CASE
WHEN b.qtr =3D 1 THEN 3
WHEN b.qtr =3D 2 THEN 6
WHEN b.qtr =3D 3 THEN 9
WHEN b.qtr =3D 4 THEN 12
END)
/
Yuval.
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Igor Neyman
Sent: Tuesday, November 09, 2004 4:31 PM
To: ChrisStephens@(protected); oracle-l@(protected)
Subject: RE: Basic sql
SELECT COUNT(*) FROM a
WHERE EXISTS (SELECT *
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 (3*b.qtr - 2) and
3*b.qtr
/
Igor Neyman, OCP DBA
ineyman@(protected)
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Stephens, Chris
Sent: Tuesday, November 09, 2004 4:11 PM
To: oracle-l@(protected)
Subject: Basic sql
Well I 've had my coffee and I still can 't get this to work:
SELECT COUNT(*) FROM a
WHERE EXISTS (SELECT *=3D20
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:
Thank you for the extra eyes!
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
This transmission may contain information that is privileged, =
confidential and exempt from disclosure under applicable law. If you, =
oracle-l@(protected), are not the intended recipient, you are hereby =
notified that any disclosure, copying, distribution, or use of the =
information contained herein (including any reliance thereon) is =
STRICTLY PROHIBITED. If you received this transmission in error, please =
immediately contact the sender and destroy the material in its entirety, =
whether in electronic or hard copy format.
