Solved

CONNECT BY LEVEL <= MONTHS_BETWEEN stops at 10/31/08?

Posted on 2008-10-09
4
1,157 Views
Last Modified: 2012-05-05
The following code only creates months through 10/31/08, any clue why it tops and how to fix it?
SELECT     LAST_DAY (ADD_MONTHS (TRUNC (date '2008-01-01', 'MM'), 1 * LEVEL - 1)

                       ) MONTH

         FROM DUAL

   CONNECT BY LEVEL <= MONTHS_BETWEEN (date '2008-12-01', date '2008-01-01') + 1;

Open in new window

0
Comment
Question by:TwistYaWig
4 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 22684488
what version of Oracle?

early versions of 9i sometimes has problems with the connect by level from dual query due to a bug.  It's fixed in at least 9.2.0.6 but maybe earlier too.

try wrapping it in an inline view


select * from (
SELECT     LAST_DAY (ADD_MONTHS (TRUNC (date '2008-01-01', 'MM'), 1 * LEVEL - 1)
                       ) MONTH
         FROM DUAL
   CONNECT BY LEVEL <= MONTHS_BETWEEN (date '2008-12-01', date '2008-01-01') + 1);



you can also try modifying the query to see if the parser would figure it out better.

    SELECT   LAST_DAY(ADD_MONTHS(TRUNC(DATE '2008-01-01', 'MM'), 1 * LEVEL - 1)) month
      FROM   DUAL
CONNECT BY   ADD_MONTHS(TRUNC(DATE '2008-01-01', 'MM'), 1 * LEVEL - 1) <= DATE '2008-12-01';


the query "should" return the last day of every month in 2008 as is, if it doesn't there is a bug somewhere else, because the code is correct.


also, check if you are using some tool that only reads the first 10 rows of a result set by default.  If so, that might be your problem, since October is the 10th month it may appear
to be a problem in the query when really it could be the client.








0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 250 total points
ID: 22685657
Try this
SELECT     LAST_DAY (ADD_MONTHS (TRUNC (date '2008-01-01', 'MM'), 1 * X.lvl - 1) ) MONTH

FROM DUAL, 

(select level lvl from dual CONNECT BY LEVEL <= MONTHS_BETWEEN (date '2008-12-01', date '2008-01-01') + 1) X;

   

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle query help 29 77
one-way data "masking" MD5 sql 26 146
Getting a return value from an IN OUT parameter in Oracle? 7 44
Shredding xml into an oracle 11g Database 2 29
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now