RE: Basic sql

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Wed, 10 Nov 2004 09:30:31 +0100
Message-Id: <200411100830.iAA8UViC021775@webmail.nexlink.net>

Chris,

I think I goofed in the month-to-quarter conversion :(.

It would probably be better (and more understandable) to use

to_number(to_char(a.op_date, 'Q')) to get the quarter.

Regards,

Stephane Faroult

RoughSea Ltd
http://www.roughsea.com

On Tue, 9 Nov 2004 17:03 , 'Stephens, Chris' <ChrisStephens_at_pqa.com> sent:

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_at_freelists.org[1]
[oracle-l-bounce_at_freelists.org[2]','','','')">oracle-l
-bounce_at_freelists.org[3][]On Behalf Of Arnon, Yuval
Sent: Tuesday, November 09, 2004 4:43 PM To: oracle-l_at_freelists.org[4]
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_at_freelists.org[5]
[oracle-l-bounce_at_freelists.org[6]','','','')">oracle-l
-bounce_at_freelists.org[7][]On Behalf Of Igor Neyman
Sent: Tuesday, November 09, 2004 4:31 PM To: ChrisStephens_at_pqa.com[8]; oracle-l_at_freelists.org[9] 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_at_perceptron.com[10]

-----Original Message-----

From: oracle-l-bounce_at_freelists.org[11]
[oracle-l-bounce_at_freelists.org[12]','','','')">oracle-l
-bounce_at_freelists.org[13]On Behalf Of Stephens, Chris
Sent: Tuesday, November 09, 2004 4:11 PM To: oracle-l_at_freelists.org[14]
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!
--

http://www.freelists.org/webpage/oracle-l[15]

--

http://www.freelists.org/webpage/oracle-l[16]

This transmission may contain information that is privileged, =3D confidential and exempt from disclosure under applicable law. If you, = =3D
oracle-l_at_freelists.org[17], 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.

--

http://www.freelists.org/webpage/oracle-l[18]
--

http://www.freelists.org/webpage/oracle-l[19]

Links --- 1 javascript:parent.opencompose('oracle-l-bounce_at_freelists.org','','','') 2 javascript:parent.opencompose('<a href= 3 javascript:parent.opencompose('oracle-l-bounce_at_freelists.org','','','') 4 javascript:parent.opencompose('oracle-l_at_freelists.org','','','') 5 javascript:parent.opencompose('oracle-l-bounce_at_freelists.org','','','') 6 javascript:parent.opencompose('<a href= 7 javascript:parent.opencompose('oracle-l-bounce_at_freelists.org','','','') 8 javascript:parent.opencompose('ChrisStephens_at_pqa.com','','','') 9 javascript:parent.opencompose('oracle-l_at_freelists.org','','','') 10 javascript:parent.opencompose('ineyman_at_perceptron.com','','','') 11 javascript:parent.opencompose('oracle-l-bounce_at_freelists.org','','','') 12 javascript:parent.opencompose('<a href= 13 javascript:parent.opencompose('oracle-l-bounce_at_freelists.org','','','') 14 javascript:parent.opencompose('oracle-l_at_freelists.org','','','') 15 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l 16 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l 17 javascript:parent.opencompose('oracle-l_at_freelists.org','','','') 18 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l 19 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 10 2004 - 02:13:19 CST