Good, I did that and it works perfect. Thx a lot Michael.
On Mon, Oct 13, 2008 at 4:43 PM, Michael Moore <[EMAIL PROTECTED]>wrote:
> yes, both the begin and the end. ;-)
>
>
> On Mon, Oct 13, 2008 at 12:47 PM, Harry Standley <[EMAIL PROTECTED]
> > wrote:
>
>> 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
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>> "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
-~----------~----~----~----~------~----~------~--~---
