RE: Basic sql
- From: "Stephens, Chris" <ChrisStephens@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Nov 2004 17:03:59 -0500
thanks! Except now I'm worried about the accuracy:
1 select count(*)
2 from a
3 where (op_year, unit_id, 1 + floor(extract(month from
a.op_date)/4))
4 in (select b.year, unit_id, qtr
5 from b
6* where b.batch_table =3D 'UNT_HRLY')
SQL> /
COUNT(*)
----------
38210
Elapsed: 00:00:01.03
SQL> ed
Wrote file afiedt.buf
1 SELECT COUNT(*) FROM a
2 WHERE EXISTS (SELECT *
3 FROM b
4 WHERE b.batch_table=3D'UNT_HRLY'
5 and a.unit_id =3D b.unit_id
6 and a.op_year =3D b.year
7* and extract(month from a.op_date) BETWEEN (3*b.qtr - 2) and
3*b.qtr )
8 /
COUNT(*)
----------
40762
Elapsed: 00:01:09.01
....none of the columns have NULL values after I filter table b on
batch_table so I know there's nothing funky there.
I'm going to look at it some more but I don't see why the different row
counts?
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Arnon, Yuval
Sent: Tuesday, November 09, 2004 4:43 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Basic sql
You can try this if you still want to use the case statement
SELECT COUNT(*) FROM a
WHERE EXISTS (SELECT *=3D3D20
FROM b
WHERE a.unit_id =3D3D b.unit_id
and a.op_year =3D3D b.op_year
and extract(month from a.op_date) BETWEEN
CASE
WHEN b.qtr =3D3D 1 THEN 1
WHEN b.qtr =3D3D 2 THEN 4=3D20
WHEN b.qtr =3D3D 3 THEN 7=3D20
WHEN b.qtr =3D3D 4 THEN 10=3D20
END
AND
CASE
WHEN b.qtr =3D3D 1 THEN 3
WHEN b.qtr =3D3D 2 THEN 6
WHEN b.qtr =3D3D 3 THEN 9
WHEN b.qtr =3D3D 4 THEN 12
END)
/
Yuval.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Igor Neyman
Sent: Tuesday, November 09, 2004 4:31 PM
To: ChrisStephens@xxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Basic sql
SELECT COUNT(*) FROM a
WHERE EXISTS (SELECT *
FROM b
WHERE a.unit_id =3D3D b.unit_id
and a.op_year =3D3D b.op_year
and extract(month from a.op_date) BETWEEN (3*b.qtr - 2) and
3*b.qtr /
Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Stephens, Chris
Sent: Tuesday, November 09, 2004 4:11 PM
To: oracle-l@xxxxxxxxxxxxx
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 *=3D3D20
FROM b
WHERE a.unit_id =3D3D b.unit_id
and a.op_year =3D3D b.op_year
and extract(month from a.op_date) BETWEEN CASE
WHEN b.qtr =3D3D 1 THEN 1 AND 3
WHEN b.qtr =3D3D 2 THEN 4 AND 6
WHEN b.qtr =3D3D 3 THEN7 AND 9
WHEN b.qtr =3D3D 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
--
//www.freelists.org/webpage/oracle-l
This transmission may contain information that is privileged, =3D
confidential and exempt from disclosure under applicable law. If you, =
=3D
oracle-l@xxxxxxxxxxxxx, are not the intended recipient, you are hereby =
=3D
notified that any disclosure, copying, distribution, or use of the =3D
information contained herein (including any reliance thereon) is =3D
STRICTLY PROHIBITED. If you received this transmission in error, please
=3D immediately contact the sender and destroy the material in its
entirety, =3D whether in electronic or hard copy format.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
