From oracle-l-bounce@freelists.org Wed Nov 10 02:13:19 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id iAA8DDM08181 for ; Wed, 10 Nov 2004 02:13:13 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id iAA8DCa08175 for ; Wed, 10 Nov 2004 02:13:12 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A650772D090; Wed, 10 Nov 2004 03:19:27 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10641-43; Wed, 10 Nov 2004 03:19:27 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EB1DB72D28E; Wed, 10 Nov 2004 03:19:21 -0500 (EST) Date: Wed, 10 Nov 2004 09:30:31 +0100 Message-Id: <200411100830.iAA8UViC021775@webmail.nexlink.net> Content-Disposition: inline Content-Transfer-Encoding: 8bit Mime-Version: 1.0 From: Stephane Faroult To: , "Chris'" Subject: RE: Basic sql Content-type: text/plain X-Origin: 195.115.41.103 X-archive-position: 12077 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: sfaroult@roughsea.com Precedence: normal Reply-To: sfaroult@roughsea.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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' 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@freelists.org[1] [oracle-l-bounce@freelists.org[2]','','','')">oracle-l -bounce@freelists.org[3][]On Behalf Of Arnon, Yuval Sent: Tuesday, November 09, 2004 4:43 PM To: oracle-l@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@freelists.org[5] [oracle-l-bounce@freelists.org[6]','','','')">oracle-l -bounce@freelists.org[7][]On Behalf Of Igor Neyman Sent: Tuesday, November 09, 2004 4:31 PM To: ChrisStephens@pqa.com[8]; oracle-l@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@perceptron.com[10] -----Original Message----- From: oracle-l-bounce@freelists.org[11] [oracle-l-bounce@freelists.org[12]','','','')">oracle-l -bounce@freelists.org[13]On Behalf Of Stephens, Chris Sent: Tuesday, November 09, 2004 4:11 PM To: oracle-l@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@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@freelists.org','','','') 2 javascript:parent.opencompose('