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
>
Received on Sat Apr 05 2003 - 14:36:51 CST
