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 >> >> >> >> --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
