Re: Inserting Records Into a table

B. Williams wrote:
> "Ed Prochak" <edprochak_at_gmail.com> wrote in message
> news:1157660418.704290.80130_at_h48g2000cwc.googlegroups.com...
> > You are on the right path. Exactly what confuses you? All you need at
> > this point is another loop to scan thru the days, inserting each pair
> > (month,date). I'll refrain from further hints until your next post. So
> > try the next step and see what you get. Come back if you need more
> > help, or if you solve it. (It is nice when the discuaaion loop can be
> > closed.)
> >
> > Ed
> >
> Ed,
> I can get the loop to isert the months into the table using the following
> code.
>
> FOR i IN 1..12 LOOP
> INSERT INTO yeardates
> (year_month)
> VALUES(varray_name(i));
> END LOOP;
>
> I can also get the loop to insert the date using a similiar loop
>
> FOR j IN 1..31 LOOP
> INSERT INTO yeardates
> (year_day)
> VALUES(varray_date(j));
> My problem is that I can't figure out how to get my output to display the
> (month day) format and even more confusing is that I can't figure out how
> to have January display 31 dates while having February displays only 28 and
> so on.
>
> I need to make a loop for each month that will display that month along with
> the days of that month.
>
> Can you assist me with that.

Are you maybe making it more difficult than necessary for the learning experience? Take a look at this statement, no looping required: INSERT INTO MY_TABLE
SELECT
  TO_CHAR(TO_DATE('01/01/2006','MM/DD/YYYY')+(ROWNUM-1),'MONTH') YEAR_MONTH, TO_NUMBER(TO_CHAR(TO_DATE('01/01/2006','MM/DD/YYYY')+(ROWNUM-1),'DD')) YEAR_DAY
FROM
  ALL_OBJECTS
WHERE
  ROWNUM<=366
  AND TO_DATE('01/01/2006','MM/DD/YYYY')+(ROWNUM-1)<'01-JAN-2007';

In the above, I am using the ALL_OBJECTS view to seed a counter, in this case from 1 to 366 (to account for years with leap year days) - you can use any table with at least 366 rows. By using January 1, 2006 as the starting date, and adding to that (ROWNUM - 1) days, we obtain all of the dates for 2006. All that you need to do is to format the dates as required. Note the check at the end to make certain that we do not include dates in the next year.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Sep 07 2006 - 18:51:25 CDT