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?
Best Answer
I don't see how this query could ever have returned the current day.
ROWNUM
starts with 1 soTRUNC(sysdate - rownum)
will never return the current day and neither willTRUNC(sysdate + rownum)
. Both sides of yourUNION
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.
You can also write it without any
UNION
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 aUNION ALL
rather than aUNION
to avoid an unnecessary sort andDISTINCT
(UNION
has to remove duplicates,UNION ALL
does not).