Re: Hotsos Symposium, Day 1

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@xxxxxxxxxxx
> 
> 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@xxxxxxxxxxx and 
> include the word "subscribe" in either the subject or body.
> 
> 
> Tuesday, March 8, 2005, 7:47:22 AM, Niall Litchfield 
> (niall.litchfield@xxxxxxxxx) wrote:
> NL> On Tue, 8 Mar 2005 08:28:11 -0500, Thomas Day <tomday2@xxxxxxxxx> 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

Other related posts: