RE: Hotsos Symposium, Day 1

From: "Anthony Molinaro" <amolinaro@xxxxxxxx>
To: <tomday2@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 8 Mar 2005 08:56:25 -0500

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.


 - a



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Thomas Day
Sent: Tuesday, March 08, 2005 8:28 AM
To: oracle-l@xxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l

RE: Hotsos Symposium, Day 1

Other related posts: