-
suppose i've got a table named para_tab with two columns namely from_dt date ,to_dt date.
The table contains only one row with values such as
from dt='01-mar-2000' and to_dt ='31-mar-2000' .
I want to insert dummy rows starting from the from_dt and ending with the to_dt into the same table with an sql statement using the same table.
for eg:
the difference of the above mentioned dates is 31 days.
so i want to insert 31 rows into the same table with an sql
statement without using any other table.
This should be done only with and sql statement,
and only one table which contains only one row.
-
-
The problem is that he listed 2 requirements:
- Only SQL (ie no PL/SQL)
- No other tables
Personally, I always create a DateDimension_T table that holds a record for every day across several years. It is then very easy to turn a date range into the proper number of rows. However, my use of a secondary table is also precluded.
I'd be interesed in what you get back for this one. Short of hard-coding the proper number of unions, I don't think it can be done. The UNION solution would be something like joining your table against
SELECT 1 FROM DUAL UNION SELECT 2 FROM DUAL...
using the end_date minus the from_date to join, then adding the from_date back to the result to generate the target date. I'd explain it better, but it's a terrible solution anyway, as you are limited by the number of UNIONs you do. It becomes very hard to handle a multi-year range :)
(Watching to see if somebody finds something)
- Chris
-
I'm not sure if I understand Sudhir's requirements correctly - this is what I *think* he wants:
- He has a the starting and ending date in a table and he wants to insert one record for each day between this two dates. He said he want's to use only SQL and no other table. Since Chris have already mention the use of DUAL (which *is* "another table") I suppose the usage of public views like ALL_OBJECTS is allowed. So how about this:
INSERT INTO para_tab(from_dt)
SELECT pt.from_dt+x.row_num-1
FROM para_tab pt,
(SELECT rownum row_num FROM all_objects
WHERE ROWNUM <= (SELECT to_dt - from_dt +1 FROM para_tab)) x;
This will insert a single row for every date between the reference dates (from_dt and to_dt). The join
obviously results in cartesian merge join, but as there is only one row in PARA_TAB initialy this bares no performance penalty. BTW, in 8i any user has (at least) about 10.000 rows available in ALL_OBJECTS, so this query can span a date range of at least about 30 years.
HTH,
-
Too, too sweet!!!
I can't believe I missed that. That is sooo cool!!!
You replaced my hokey UNIONed list with a readily-available one - very nice!
Of course, just to do my usual Devil's advocate bit, there *are* some minor issues with such an approach.
- The Oracle 'views' are generally terrible performance-wise. Using them in regular SQL is a chancy thing at best. Again, I love this solution - just trying to show the problems as well.
- You can't do an EXPLAIN PLAN on a query that uses an Oracle view unless you have major rights, which most people do not. This makes it even riskier to include these views in complicated or time-intensive SQL.
- The meaning of what you are doing is a little hidden. Having an actual DateDimension table makes the functionality obvious, and optimizable, with everything being completely under your control.
However, given the incredible restrictions of this particular post, jmodic turned in an outstanding answer!
- Chris
