Multi rows generating with “select from dual”

Sometimes you may need to get some rows out of your magic hat. So, what’s the trick? Ebusy had the need this morning to filter a “select” query to only the last 6 months. Of course there are several possibilities to do this, but because I needed to keep all the last six months even if there were no records for each of them, I chose to do a right join with a “last six months generating” query.

So, you don’t need a table with months. You only need to correctly generate them from DUAL.

SELECT TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, ‘MM’), -1 * LEVEL), ‘MONTH’ ) months
FROM DUAL
CONNECT BY LEVEL <= 6

MONTHS
————–
AUGUST
JULY
JUNE
MAY
APRIL
MARCH

The output, as you can see, is the last 6 months for a September current month. If you need last 6 months including current month:

SELECT TO_CHAR (ADD_MONTHS ( ADD_MONTHS(TRUNC (SYSDATE, ‘MM’), 1), -1 * LEVEL), ‘MONTH’ ) months
FROM DUAL
CONNECT BY LEVEL <= 6

MONTHS
—————–
SEPTEMBER
AUGUST
JULY
JUNE
MAY
APRIL

Of course, you may output the dates as you wish (as you need), this is a “months” example. Now, why did I need this?

SELECT how_many_books, last_six_months.month per_month FROM
(
(SELECT count(book_id) how_many_books, month FROM books GROUP BY month) books_per_month
RIGHT JOIN
(SELECT TO_CHAR (ADD_MONTHS ( ADD_MONTHS(TRUNC (SYSDATE, ‘MM’), 1), -1 * LEVEL), ‘MONTH’ ) month
FROM DUAL
CONNECT BY LEVEL <= 6) last_six_months
ON last_six_months.month = books_per_month.month
)

how_many_books | per_month
—————————————
0 | SEPTEMBER
24 | AUGUST
3 | JULY
0 | JUNE
12 | MAY
9 | APRIL

As you can see I did a right join without needing another table.

Although it’s easy to use CONNECT BY for generating lots of rows, you should do it safely. It’s memory consuming (and it’s not the workarea memory, therefore the normal PGA size restriction constraints like PGA_AGGREGATE_TARGET don’t apply) and for more complex queries (with more rows) you should use cartesian merge joins. Please read more on http://blog.tanelpoder.com/2008/06/08/generating-lots-of-rows-using-connect-by-safely/.