Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This should be pretty reproducible I hope.

I'm on Oracle 11g. This query, which I use as part of another query, is acting strangely:

SELECT TRUNC(SYSDATE - ROWNUM) DATEITEM
                         FROM   DUAL
                         CONNECT BY ROWNUM <= 32
                         UNION
                         SELECT TRUNC(SYSDATE + ROWNUM) DATEITEM
                         FROM   DUAL
                         CONNECT BY ROWNUM <= 32
order by dateitem desc;

This should return 32 days before and after today's date, in descending order.

It has always worked, and always had, until today.

For some reason, this does not return September 26th. It skips right from 25 to 27th.

I cannot figure a rhyme or reason for this. This query did return the 25th on the 25th for example (and so I have no evidence that it leaves out only sysdate, as it worked yesterday).

What the heck have I done wrong here?

share|improve this question
add comment

3 Answers 3

I don't see how this query could ever have returned the current day. ROWNUM starts with 1 so TRUNC(sysdate - rownum) will never return the current day and neither will TRUNC(sysdate + rownum). Both sides of your UNION return exactly 32 rows so the entire query should always return 64 rows. If you want 32 days before today, 32 days after today, and today in your result set, it would need to be 65 rows.

This query should return 65 rows and should include today.

SELECT TRUNC(SYSDATE - ROWNUM) DATEITEM
FROM   DUAL
CONNECT BY ROWNUM <= 32
UNION
SELECT TRUNC(SYSDATE + ROWNUM - 1) DATEITEM
FROM   DUAL
CONNECT BY ROWNUM <= 33
order by dateitem desc;

You can also write it without any UNION

SELECT TRUNC(SYSDATE - ROWNUM -33) DATEITEM
FROM   DUAL
CONNECT BY ROWNUM <= 65
order by dateitem desc;

From a performance standpoint, it is unlikely that there will be a meaningful difference between using one, two, or three subqueries. If you are going to UNION multiple sets together, since you know there are no overlaps, you would want to use a UNION ALL rather than a UNION to avoid an unnecessary sort and DISTINCT (UNION has to remove duplicates, UNION ALL does not).

share|improve this answer
    
Hi Justin, per my previous answer I also noted that taking the query into account, I decided that there was no way my user could have been running the query as she claimed she was, because it (as you correctly state) would have never returned the current date. Thank you for answering! –  SeanKilleen Sep 27 '12 at 1:19
    
Also, If you'd be willing to comment, I'm interested in the speed implications of my answer, because I think it is more readable, though includes a separate union statement. I'm thinking the speed difference should be negligible given the scope. –  SeanKilleen Sep 27 '12 at 1:20
    
@goober - It is unlikely that there are meaningful performance differences between the alternatives so whatever option is the most readable to you would be preferred. You would want to do a UNION ALL rather than a UNION. –  Justin Cave Sep 27 '12 at 1:51
add comment

I decided that since it looked wrong, I'd ignore my user's comments that it worked yesterday and union in the sysdate as well to ensure that it always shows up.

The following is the part of the query now, working:

SELECT TRUNC(SYSDATE - ROWNUM) DATEITEM
FROM   DUAL
CONNECT BY ROWNUM <= 32

UNION
SELECT TRUNC(SYSDATE) DATEITEM
FROM DUAL

UNION
SELECT TRUNC(SYSDATE + ROWNUM) DATEITEM
FROM   DUAL
CONNECT BY ROWNUM <= 32

order by dateitem desc;
share|improve this answer
add comment

you can simulate the execution of your query on a specific date by the following query (change both date strings of the query to the day you want to investigate)

SELECT TRUNC(to_date('2012-09-26','YYYY-MM-DD') - ROWNUM) DATEITEM
                     FROM   DUAL
                     CONNECT BY ROWNUM <= 32
                     UNION
                     SELECT TRUNC(to_date('2012-09-26','YYYY-MM-DD') + ROWNUM) DATEITEM
                     FROM   DUAL
                     CONNECT BY ROWNUM <= 32
order by dateitem desc;

As @JustinCave analyzes this will never return the "current" date.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.