From oracle-l-bounce@freelists.org Tue Mar 8 10:09:33 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j28G9XOX013246 for ; Tue, 8 Mar 2005 10:09:33 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j28G9Tem013236 for ; Tue, 8 Mar 2005 10:09:30 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 372717F3D7; Tue, 8 Mar 2005 10:07:43 -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 29591-08; Tue, 8 Mar 2005 10:07:43 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B23147EA22; Tue, 8 Mar 2005 10:07:42 -0500 (EST) Message-ID: <422DBF41.8030607@raytheon.com> Date: Tue, 08 Mar 2005 09:05:37 -0600 From: Michael Twaddell User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: jonathan@gennick.com Cc: oracle-l@freelists.org Subject: Re: Hotsos Symposium, Day 1 References: <6292852314.20050307233330@gennick.com> <7765c897050308054729602479@mail.gmail.com> <138124060028.20050308081338@gennick.com> In-Reply-To: <138124060028.20050308081338@gennick.com> Content-Transfer-Encoding: 8bit Content-type: text/plain; charset=ISO-8859-1 X-SPAM: 0.00 X-archive-position: 17060 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: twaddell@raytheon.com Precedence: normal Reply-To: twaddell@raytheon.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=LINES_OF_YELLING autolearn=ham version=2.60 X-Spam-Level: I'm running version 9204 and I receive only one row, but I think it has something to do with dual. If I change the query to SELECT TRUNC(SYSDATE,'YEAR') + LEVEL - 1 FROM (SELECT 'X' FROM DUAL WHERE rownum = 1) CONNECT BY 1 = 1 AND LEVEL < TRUNC(SYSDATE+366,'YEAR') - TRUNC(SYSDATE,'YEAR') + 1 it works properly. Also if I create a table, t, with only 1 row and substitute it for dual, it also works. example: SQL> create table t (c varchar2(1)); Table created. SQL> insert into t values ('x'); 1 row created. SQL> SELECT TRUNC(SYSDATE,'YEAR') + LEVEL - 1 2 from t 3 CONNECT BY 1 = 1 4 AND LEVEL < TRUNC(SYSDATE+366,'YEAR') - TRUNC(SYSDATE,'YEAR') + 1 5 / TRUNC(SYS --------- 01-JAN-05 02-JAN-05 03-JAN-05 04-JAN-05 05-JAN-05 06-JAN-05 07-JAN-05 ......... ......... 30-DEC-05 31-DEC-05 Regards, Michael T. Jonathan Gennick wrote: > NL> Its version specific, works on 10.1 but not on 9206 or 8174. > > Tom says he's filed a bug already on the 9i behavior. He thinks that > particular problem might lie in SQL*Plus itself, and not in the RDBMS. > I have no way at the moment to test that theory. > > NL> http://asktom.oracle.com/pls/ask/f?p=4950:8:8515227363337669542::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:34808326503816 > NL> has the 'trick' in evidence, but it looks like it was originally due > NL> to Mikito Harakiri. > > Yes, the technique came to Tom from Mikito. I gave credit to Mikito in > my 'blog. > > Best regards, > > Jonathan Gennick --- Brighten the corner where you are > http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com > > Join the Oracle-article list and receive one > article on Oracle technologies per month by > email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article, > or send email to Oracle-article-request@gennick.com and > include the word "subscribe" in either the subject or body. > > > Tuesday, March 8, 2005, 7:47:22 AM, Niall Litchfield (niall.litchfield@gmail.com) wrote: > NL> On Tue, 8 Mar 2005 08:28:11 -0500, Thomas Day wrote: > >>>I tried running the CONNECT BY example that you had. >>> >>>SQL> SELECT TRUNC(SYSDATE,'YEAR') + LEVEL - 1 >>> 2 FROM DUAL >>> 3 CONNECT BY 1 = 1 >>> 4 AND LEVEL < TRUNC(SYSDATE+366,'YEAR') - TRUNC(SYSDATE,'YEAR') + 1; >>> >>>TRUNC(SYS >>>--------- >>>01-JAN-05 >>> >>>1 row selected. >>> > > > NL> Its version specific, works on 10.1 but not on 9206 or 8174. > > NL> http://asktom.oracle.com/pls/ask/f?p=4950:8:8515227363337669542::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:34808326503816 > NL> has the 'trick' in evidence, but it looks like it was originally due > NL> to Mikito Harakiri. > > -- > http://www.freelists.org/webpage/oracle-l > -- http://www.freelists.org/webpage/oracle-l