Also, it help to have the code properly formatted so that you can see easily
what you are doing.The "BEGIN" right before your "IF trk_shp_wo_dtl.hrs IS
NULL"
serves no purpose. I'd take it out.
Mike
DECLARE
CURSOR work_order_cur IS
SELECT wo.unit, itm.code, wo.loc_code, wo.work_order, wo.completed,
dtl.price,
dtl.qty, dtl.hrs
FROM trk_shp_wo wo,
trk_shp_wo_dtl dtl,
trk_shp_item itm
WHERE wo.completed_flag = 'Y'
AND wo.work_order = dtl.work_order
AND itm.item_id = dtl.item_id;
v_unit trk_shp_wo.unit%TYPE;
v_code trk_shp_item.code%TYPE;
v_loc_code trk_shp_wo.loc_code%TYPE;
v_work_order trk_shp_wo.work_order%TYPE;
v_completed trk_shp_wo.completed%TYPE;
v_price trk_shp_inv.price%TYPE;
v_qty trk_shp_wo_dtl.qty%TYPE;
v_hrs trk_shp_wo_dtl.hrs%TYPE;
BEGIN
OPEN work_order_cur;
LOOP
FETCH work_order_cur
INTO v_unit, v_code, v_loc_code, v_work_order, v_completed, v_price,
v_qty, v_hrs;
EXIT WHEN work_order_cur%NOTFOUND;
--this section checks to see if hrs exist if not then do this
BEGIN
IF trk_shp_wo_dtl.hrs IS NULL
THEN
INSERT INTO vehicle_cost
(unit,
cost_code,
loc_code,
invoice_no,
invoice_date,
amount,
qty
)
VALUES (v_unit,
v_code,
v_loc_code,
v_work_order,
v_completed,
v_price * v_qty,
'0'
);
--if hrs exist then do this
ELSE
INSERT INTO vehicle_cost
(unit,
cost_code,
loc_code,
invoice_no,
invoice_date,
amount,
qty
)
VALUES (v_unit,
v_code,
v_loc_code,
v_work_order,
v_completed,
v_price * v_hrs,
v_hrs
);
END IF;
END;
END LOOP;
CLOSE work_order_cur;
END;
On Mon, Oct 13, 2008 at 12:06 PM, Michael Moore <[EMAIL PROTECTED]>wrote:
> try
> IF v_hrs IS NULL
>
>
>
> On Mon, Oct 13, 2008 at 11:15 AM, Harry Standley <[EMAIL PROTECTED]
> > wrote:
>
>> This is my first loop to create on my own. My code is as follows:
>>
>> declare
>> cursor work_order_cur is
>> select
>> wo.unit,
>> itm.code,
>> wo.loc_code,
>> wo.work_order,
>> wo.completed,
>> dtl.price,
>> dtl.qty,
>> dtl.hrs
>> from
>> trk_shp_wo wo,
>> trk_shp_wo_dtl dtl,
>> trk_shp_item itm
>> where
>> wo.completed_flag = 'Y' and
>> wo.WORK_ORDER = dtl.work_order and
>> itm.Item_id = dtl.item_id;
>>
>> v_unit trk_shp_wo.unit%type;
>> v_code trk_shp_item.code%type;
>> v_loc_code trk_shp_wo.loc_code%type;
>> v_work_order trk_shp_wo.work_order%type;
>> v_completed trk_shp_wo.completed%type;
>> v_price trk_shp_inv.price%type;
>> v_qty trk_shp_wo_dtl.qty%type;
>> v_hrs trk_shp_wo_dtl.hrs%type;
>>
>> begin
>> open work_order_cur;
>> loop fetch work_order_cur into
>> v_unit,
>> v_code,
>> v_loc_code,
>> v_work_order,
>> v_completed,
>> v_price,
>> v_qty,
>> v_hrs;
>> exit when work_order_cur%NOTFOUND;
>>
>> --this section checks to see if hrs exist if not then do this
>> begin
>> if trk_shp_wo_dtl.hrs is null
>> then
>> insert into vehicle_cost
>> (
>> unit,
>> cost_code,
>> loc_code,
>> invoice_no,
>> invoice_date,
>> amount,
>> qty
>> )
>> values
>> (
>> v_unit,
>> v_code,
>> v_loc_code,
>> v_work_order,
>> v_completed,
>> v_price * v_qty,
>> '0'
>> );
>> --if hrs exist then do this
>> else
>>
>> insert into vehicle_cost
>> (
>> unit,
>> cost_code,
>> loc_code,
>> invoice_no,
>> invoice_date,
>> amount,
>> qty
>> )
>> values
>> (
>> v_unit,
>> v_code,
>> v_loc_code,
>> v_work_order,
>> v_completed,
>> v_price * v_hrs,
>> v_hrs
>> );
>>
>> end if;
>> end;
>> end loop;
>> close work_order_cur;
>> end;
>>
>> When I run it it dies on
>>
>> "if trk_shp_wo_dtl.hrs is null "
>>
>> Tell me that it is not allowed in this context.
>>
>> What I am attempting to do is update another table and I want the firleds
>> it enters based upon the if statement conditions. I guess my first question
>> is can you use and IF...THEN...ELSE here or not?
>>
>> --
>> H
>>
>> YIM [EMAIL PROTECTED]
>> MSN [EMAIL PROTECTED]
>>
>> "When one teaches, two learn."
>> Robert Half
>>
>> >>
>>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
