Re: PL/SQL question

clint wrote:

> I realize that.....
> the problem is that the last solution I was provided...does not meet all my
> needs....
>
> here are the tables I am dealing with.......
>
> MILE_LOG
> Name Null? Type
> ----------------------------------------- -------- ------------------------
> ----
> EMPLO_ID NOT NULL NUMBER(6)
> NUM_MILES NOT NULL NUMBER(8,2)
> MIL_ALLOWANCE NOT NULL NUMBER(2,2)
> ENTRY_DATE DATE
> DESCR VARCHAR2(25)
>
> EXPENSE_LOG
> Name Null? Type
> ----------------------------------------- -------- ------------------------
> ----
> ENTRY_ID NOT NULL NUMBER(6)
> EMPLO_ID NOT NULL NUMBER(6)
> EXP_ID NOT NULL NUMBER(6)
> AMOUNT NUMBER(6,2)
> ENTRY_DATE DATE
> DESCR VARCHAR2(25)
> MILEAGE NUMBER(6)
>
> I want to take info from table 1(mile_log), make a small calculation....then
> add it to table 2 (expense_log).
>
> If you notice, the second table, the destination table, has more columns
> then the mile_log table, so some information has to also be plugged in
> there. Since this script will only be used between these 2 tables....I have
> come up with the following script that , so far, only seems to work....
>
> Any other suggestions would be welcome.....
>
> I am a newbie please be gentle:)
>
> DECLARE
> CURSOR c_MILEAGE_CAL IS
> SELECT EMPLO_ID, NUM_MILES, MIL_ALLOWANCE, ENTRY_DATE, DESCR
> FROM MILE_LOG;
> V_EMPLOID MILE_LOG.EMPLO_ID%TYPE;
> v_EXPID EXPENSE_LOG.EXP_ID%TYPE :=11004;
> V_NUM_MILES MILE_LOG.NUM_MILES%TYPE;
> V_CENTSPERMILE MILE_LOG.MIL_ALLOWANCE%TYPE;
> V_DATE DATE;
> V_DESCR MILE_LOG.DESCR%TYPE;
> V_REFUND NUMBER(8,2);
> BEGIN
> OPEN c_MILEAGE_CAL;
> FETCH c_MILEAGE_CAL INTO
> V_EMPLOID, V_NUM_MILES, V_CENTSPERMILE, V_DATE, V_DESCR;
> V_REFUND := V_NUM_MILES * V_CENTSPERMILE;
> INSERT INTO EXPENSE_LOG
> VALUES(EXPLOG_COUNT_SEQ.NEXTVAL,V_EMPLOID,V_EXPID,
> V_REFUND,V_DATE, V_DESCR, V_NUM_MILES );
> EXIT WHEN c_MILEAGE_CAL%NOTFOUND;
> CLOSE c_MILEAGE_CAL;
> COMMIT;
> END;
>
> "DA Morgan" <damorgan_at_exxesolutions.com> wrote in message
> news:3E8F2522.E001462A_at_exxesolutions.com...
> > clint wrote:
> >
> > > I just enter it and it does not function.....it still ask me to
> > > declare the variables and the # of columns and the columns I want and
> not
> > > matching up......
> > >
> > > There are columns in the destination table that do no correspond to the
> > > first table.....i need those columns to be filled in as well...
> > > this script wil be part of a trigger that will fire on a per record
> > > basis...
> > >
> > > Please advise...
> > >
> > > "andrewst" <member14183_at_dbforums.com> wrote in message
> > > news:2729358.1049465317_at_dbforums.com...
> > > >
> > > > Originally posted by Andrew Hardy
> > > > > Clint,
> > > > >
> > > > > > BEGIN
> > > > > > OPEN c_MILEAGE_CAL;
> > > > > > LOOP
> > > > > > EXIT WHEN c_MILEAGE_CAL%NOTFOUND;
> > > > > > FETCH c_MILEAGE_CAL INTO
> > > > > > V_EMPLOID, V_NUM_MILES, V_CENTSPERMILE, V_DATE, V_DESCR;
> > > > > > V_REFUND := V_NUM_MILES * V_CENTSPERMILE;
> > > > > > INSERT INTO EXPENSE_LOG
> > > > > > VALUES(EXPLOG_COUNT_SEQ.NEXTVAL,V_EMPLOID,11004,V_REFUND,V_DA-
> > > > > TE,
> > > > > V_DESCR,
> > > > > > V_NUM_MILES );
> > > > > > END LOOP;
> > > > > > CLOSE c_MILEAGE_CAL;
> > > > > > COMMIT;
> > > > > > END;
> > > > >
> > > > > Try putting the FETCH before the EXIT WHEN. The way you have it
> > > > > the FETCH
> > > > > fails, a row is inserted and then you exit the loop.
> > > > >
> > > > > Andy
> > > >
> > > > Or better still, use the excellent PL/SQL FOR loop, which saves a lot
> of
> > > > code AND prevents you from making silly mistakes like that:
> > > >
> > > >
> > > > DECLARE
> > > > CURSOR c_MILEAGE_CAL IS
> > > > SELECT EMPLO_ID, NUM_MILES, MIL_ALLOWANCE, ENTRY_DATE, DESCR,
> > > > V_NUM_MILES * V_CENTSPERMILE REFUND
> > > > FROM MILE_LOG;
> > > > BEGIN
> > > > FOR r IN c_MILEAGE_CAL LOOP
> > > > INSERT INTO EXPENSE_LOG -- Please list the EXPENSE_LOG columns
> here
> > > !!!!
> > > >
> VALUES(EXPLOG_COUNT_SEQ.NEXTVAL,R.EMPLOID,11004,R.REFUND,R.ENTRY_DATE,
> > > R.DESCR,
> > > > R.NUM_MILES );
> > > > END LOOP;
> > > > COMMIT;
> > > > END;
> > > >
> > > > Note the complete absence of variable declarations!
> > > >
> > > > Or, even better (simpler AND faster):
> > > >
> > > >
> > > > BEGIN
> > > > INSERT INTO EXPENSE_LOG -- Please list the EXPENSE_LOG columns
> here!!!!
> > > > SELECT EMPLO_ID, NUM_MILES, MIL_ALLOWANCE, ENTRY_DATE, DESCR,
> > > > V_NUM_MILES * V_CENTSPERMILE REFUND
> > > > FROM MILE_LOG;
> > > > COMMIT;
> > > > END;
> > > >
> > > > (Feeling of deja vu here... haven't I made these same suggestions
> > > > before?)
> > > >
> > > > --
> > > > Posted via http://dbforums.com
> >
> > Type the following a the SQL*Plus command prompt:
> >
> > SQL> desc <table_name>
> >
> > look at the column where it says "NOT NULL".
> >
> > You must provide values for each and every one of these.
> >
> > Daniel Morgan
> >

I don't mean to be harsh here but we are not required to" meet all of your needs." You need to use what was provided as a guide to solve the problem yourself.

Unless, of course, you are willing to step up to the plate with someone's hourly consulting rate.

You have more than enough information to solve this yourself or you should find a mentor at work to help you learn to do your job.

Daniel Morgan Received on Sat Apr 05 2003 - 17:47:07 CST