Re: Hotsos Symposium, Day 1

From: Michael Twaddell <twaddell_at_raytheon.com>
Date: Tue, 08 Mar 2005 09:05:37 -0600
Message-ID: <422DBF41.8030607@raytheon.com>

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_at_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_at_gmail.com) wrote:
> NL> On Tue, 8 Mar 2005 08:28:11 -0500, Thomas Day <tomday2_at_gmail.com> 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

Received on Tue Mar 08 2005 - 10:09:33 CST