Re: Inserting Records Into a table

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 7 Sep 2006 18:45:49 -0700
Message-ID: <1157679949.390916.41560@i3g2000cwc.googlegroups.com>

B. Williams wrote:
> "Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message
> news:1157673085.523104.66250_at_m73g2000cwd.googlegroups.com...
> > 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.
> >
> Charles, I can do this in different ways as seen below.
>
> set serveroutput on size 1000000
>
> Declare
> Type name_varray is varray(12) of varchar2(20);
> varray_name name_varray :=
> name_varray(null,null,null,null,null,null,null,null,null,null,null,null);
>
> vLastDay number;
> vFirstDay varchar2(20);
>
> v_counter BINARY_INTEGER := 0;
>
> begin
>
> varray_name(1) := 'January';
> varray_name(2) := 'February';
> varray_name(3) := 'March';
> varray_name(4) := 'April';
> varray_name(5) := 'May';
> varray_name(6) := 'June';
> varray_name(7) := 'July';
> varray_name(8) := 'August';
> varray_name(9) := 'September';
> varray_name(10) := 'October';
> varray_name(11) := 'November';
> varray_name(12) := 'December';
>
> dbms_output.put_line('Year_Month Year_Day');
>
> for i in 1..12 loop
>
> vFirstDay := varray_name(i)||' 1 '||to_char(sysdate, 'YYYY');
>
> vLastDay := to_number(to_char(last_day(to_date(vFirstDay, 'Month DD
> YYYY')), 'DD')); -- compute last day of 'current' month
>
> for cdate in 1..vLastDay loop
>
> dbms_output.put_line(rpad(varray_name(i),10)||' '||cdate);
> v_counter := v_counter + 1;
> end loop;
>
> end loop;
> dbms_output.put_line('Total number of ' || v_counter ||' records were
> inserted.');
> end;
> /
>
> but, I really want to accomplish this task while meeting all of the
> requirements.

OK, looking over the requirements again: "The end result is supposed to list
the two columns with the month under the month column and the date under the
day column. It also requires using a seperate loop for every month and a
counter to keep track of the total records inserted. I have a good understanding of counter so I don't need any assistance with that. I created
a loop for the months, but it is rather long and it doesn't meet the requirement for the day."

It still looks like you are still working too hard at the problem. The solution in pseudo code (actually VB syntax):

    Dim m As Integer
    Dim d As Integer
    Dim varStartMonth As Variant 'This is a date     Dim varMonth As Variant 'This is a date     Dim strMonth As String 'This is a VARCHAR2     Dim intDay As Integer
    Dim intCounter As Integer

    varStartMonth = CDate("01/01/2006") 'Set the first day of the year     intCounter = 0 'Zero the counter, not required in some languages

    For m = 1 To 12 'Loop through the 12 months

        varMonth = DateAdd("m", m - 1, varStartMonth) 'Record the numeric value of the first day of each month

        strMonth = Format(DateAdd("m", m - 1, varStartMonth), "MMMM") 'Record the month name for each month

        For d = 1 To 31 'Loop through up to 31 days in the month
            If Format(DateAdd("d", d - 1, varMonth), "MMMM") = strMonth
Then
                'This date is still in the same month
                'Output the strMonth (month name) and the value of d
(day of the year)
                Debug.Print strMonth, d
                intCounter = intCounter + 1 'Increase the counter
            End If
        Next d

    Next m

Help in the translation:
DateAdd("m", m - 1, varStartMonth) === ADD_MONTHS(varStartMonth, m - 1)
DateAdd("d", d - 1, varMonth) === varMonth + (d -1) CDate("01/01/2006") === TO_DATE('01/01/2006', 'MM/DD/YYYY')

I will let you finish the translation. If you complete the translation, and it works in a PL/SQL program, post the results.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Sep 07 2006 - 20:45:49 CDT