Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Problem
SQL Query Problem [message #200049] Fri, 27 October 2006 12:07 Go to next message
pankyz
Messages: 11
Registered: October 2006
Junior Member

Hi Guys,
Joined this forum just recently. I have a Problem in SQL that i want to display 50 dates on using sql for.eg starting date is 1st april,2006 and now i want to display 50 dates from that date onward.I dont want to use any cursor or procedure..Just wanna do it using Query.
Thanks
Re: SQL Query Problem [message #200070 is a reply to message #200049] Fri, 27 October 2006 14:12 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
select trunc(sysdate) + level  from dual
 connect by level <= 10;
 
TRUNC(SYSDATE)+LEVEL
10/28/2006
10/29/2006
10/30/2006
10/31/2006
11/1/2006
11/2/2006
11/3/2006
11/4/2006
11/5/2006
11/6/2006


Test it thouroghly...
Re: SQL Query Problem [message #200074 is a reply to message #200049] Fri, 27 October 2006 14:25 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member


select to_date('01/04/2006','dd/mm/yyyy')+level DATE_VALUE from dual connect by level <=50


Naveen
Re: SQL Query Problem [message #200100 is a reply to message #200070] Fri, 27 October 2006 19:58 Go to previous messageGo to next message
pankyz
Messages: 11
Registered: October 2006
Junior Member

Hi thx for your reply
but ...It did not work out as you said..it is just displaying one row..
Re: SQL Query Problem [message #200103 is a reply to message #200100] Fri, 27 October 2006 20:23 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
It appears you are on 9i. Just wrap the code with select * from(...):

SQL> select trunc(sysdate) + level  from dual
  2   connect by level <= 10;

TRUNC(SYS
---------
28-OCT-06

SQL> select * from (select trunc(sysdate) + level  from dual
  2   connect by level <= 10);

TRUNC(SYS
---------
28-OCT-06
29-OCT-06
30-OCT-06
31-OCT-06
01-NOV-06
02-NOV-06
03-NOV-06
04-NOV-06
05-NOV-06
06-NOV-06

10 rows selected.
Re: SQL Query Problem [message #200104 is a reply to message #200103] Fri, 27 October 2006 20:34 Go to previous messageGo to next message
pankyz
Messages: 11
Registered: October 2006
Junior Member

Thanks Ebrian,
It is working fine now..Can u explain the query if you dnt mind and call u tell me y the previous query does not work in 9i.
Thanks and Regards
Re: SQL Query Problem [message #200106 is a reply to message #200104] Fri, 27 October 2006 22:26 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
It's a limitation with 9i, that for the recursive CONNECT BY to return more than one row, you have to use an inline view or a WITH clause.

SQL> select * from (
  2  select level  from dual
  3   connect by level <= 10);

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> with t as (
  2     select level  from dual
  3     connect by level <= 10)
  4  select * from t;

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.


As seen above, the CONNECT BY LEVEL is a hierarchical query that simply generates a list of numbers from 1 to 10 via the LEVEL pseudocolumn. Each consecutive value of LEVEL is just added to the date to generate the list you requested.

HTH
Re: SQL Query Problem [message #200121 is a reply to message #200049] Sat, 28 October 2006 04:42 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

HI
for solving your problem you should use the following query
it will include the first of april and the onward dates too.


select * from (select trunc(to_date('01-04-06','dd-mm-rr')-1) + level date_value from dual
connect by level <= 50)
DATE_VALUE
----------
01-APR-06
02-APR-06
03-APR-06
04-APR-06
05-APR-06
06-APR-06
07-APR-06
08-APR-06
09-APR-06
10-APR-06
11-APR-06
12-APR-06
13-APR-06
14-APR-06
15-APR-06
16-APR-06
17-APR-06
18-APR-06
19-APR-06
20-APR-06
21-APR-06

DATE_VALUE
----------
22-APR-06
23-APR-06
24-APR-06
25-APR-06
26-APR-06
27-APR-06
28-APR-06
29-APR-06
30-APR-06
01-MAY-06
02-MAY-06
03-MAY-06
04-MAY-06
05-MAY-06
06-MAY-06
07-MAY-06
08-MAY-06
09-MAY-06
10-MAY-06
11-MAY-06
12-MAY-06

DATE_VALUE
----------
13-MAY-06
14-MAY-06
15-MAY-06
16-MAY-06
17-MAY-06
18-MAY-06
19-MAY-06
20-MAY-06

50 rows selected.


i hope this will help you

regards

Muhammad Shahid Mughal
Re: SQL Query Problem [message #200330 is a reply to message #200121] Mon, 30 October 2006 06:48 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Can anybody explain to me why am i getting an output like this. I am asking this out of my own curiosity.

SQL> create table test (sno number);

Table created.

SQL> insert into test values (10);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

SNO
----------
10

SQL> select level from (select max(sno) rnum from test)
2 connect by level <= rnum;

LEVEL
----------
1
2
3
4
5
6
7
8
9
10

10 rows selected.

SQL> select level, rnum from (select 10 rnum from dual)
2 connect by level <= rnum;

LEVEL RNUM
---------- ----------
1 10

Thanks in advance

cheers

OOPS I forgot to tell you i am on Oracle 9i.

[Updated on: Mon, 30 October 2006 06:49]

Report message to a moderator

Re: SQL Query Problem [message #200357 is a reply to message #200330] Mon, 30 October 2006 08:44 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
S.Rajaram, it's a 9i anomaly that doesn't occur in 10gR2.
Previous Topic: Date query
Next Topic: Populating Text File with records from a table
Goto Forum:
  


Current Time: Sun Dec 04 04:49:43 CST 2016

Total time taken to generate the page: 0.22519 seconds