Re: Inserting Records Into a table

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

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