RE: Hotsos Symposium, Day 1

From: Anthony Molinaro <amolinaro_at_wgen.net>
Date: Tue, 8 Mar 2005 08:56:25 -0500
Message-ID: <D17DB304A9F42B4787B68861F9DAE61CD101EE@wgdc02.wgenhq.net>

Tom,

the "1=3D1" can be any expression that evaluates to true, ex:

SQL> with x as (
  2 SELECT TRUNC(SYSDATE,'YEAR') + LEVEL - 1   3 FROM DUAL
  4 CONNECT BY 1 is not null
  5 AND LEVEL < TRUNC(SYSDATE+366,'YEAR') - TRUNC(SYSDATE,'YEAR') + 1   6 )
  7 select * from x;

TRUNC(SYS

01-JAN-05
02-JAN-05
03-JAN-05
04-JAN-05
05-JAN-05
06-JAN-05
07-JAN-05

...

it can also be used to limit the rows (so you don't need to specify rownum in the outer query) - ex, just january:

SQL> with x as (
  2 SELECT TRUNC(SYSDATE,'YEAR') + LEVEL - 1   3 FROM DUAL
  4 CONNECT BY level <=3D
(last_day(trunc(sysdate,'y'))-trunc(sysdate,'y'))+1   5 AND LEVEL < TRUNC(SYSDATE+366,'YEAR') - TRUNC(SYSDATE,'YEAR') + 1   6 )
  7 select * from x;

TRUNC(SYS

01-JAN-05
02-JAN-05
03-JAN-05
04-JAN-05
05-JAN-05
06-JAN-05
07-JAN-05
08-JAN-05
09-JAN-05
10-JAN-05
11-JAN-05
12-JAN-05
13-JAN-05
14-JAN-05
15-JAN-05
16-JAN-05
17-JAN-05
18-JAN-05
19-JAN-05
20-JAN-05
21-JAN-05
22-JAN-05
23-JAN-05
24-JAN-05
25-JAN-05
26-JAN-05
27-JAN-05
28-JAN-05
29-JAN-05
30-JAN-05
31-JAN-05

31 rows selected.

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Day Sent: Tuesday, March 08, 2005 8:28 AM
To: oracle-l_at_freelists.org
Subject: Re: Hotsos Symposium, Day 1

I tried running the CONNECT BY example that you had.

SQL> SELECT TRUNC(SYSDATE,'YEAR') + LEVEL - 1   2 FROM DUAL
  3 CONNECT BY 1 =3D 1
  4 AND LEVEL < TRUNC(SYSDATE+366,'YEAR') - TRUNC(SYSDATE,'YEAR') + 1;

TRUNC(SYS

01-JAN-05 1 row selected.

I'm obvisously missing something. What's with the 1=3D1?

  1 SELECT TRUNC(SYSDATE,'YEAR') + LEVEL - 1   2 FROM DUAL
  3 CONNECT BY
  4 --1 =3D 1 AND
  5* LEVEL < TRUNC(SYSDATE+366,'YEAR') - TRUNC(SYSDATE,'YEAR') + 1 SQL> / TRUNC(SYS

01-JAN-05 1 row selected.
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Tue Mar 08 2005 - 08:59:44 CST