Thanks Michael that worked.
As for the formatting in my editor is looks better that it pasted.
I did take out the extra begin and I assumed I needed to take out the extra
end as well. Thanks for oyur help.
On Mon, Oct 13, 2008 at 2:10 PM, Michael Moore <[EMAIL PROTECTED]>wrote:
> 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
>>>
>>>
>>>
>>
>
> >
>
--
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
-~----------~----~----~----~------~----~------~--~---
