Home » SQL & PL/SQL » SQL & PL/SQL » A Syntax issue, whats wrong? (oracle 9i)
A Syntax issue, whats wrong? [message #282621] Thu, 22 November 2007 10:49 Go to next message
rorey_breaker
Messages: 5
Registered: October 2007
Junior Member
Hello, below is the code segment which is proving to be a problem:


DECLARE
maxorderid number(4);
CURSOR itemtoupdate IS
		SELECT i.order_id, i.customer_id, i.ship_date, st.countt
		FROM ph2_item i, sites_total st
		where i.customer_id=st.customer_id
		order by   order_date, customer_id, ship_date, item_id			FOR UPDATE OF i.order_id, i.item_id NOWAIT;			
				itemforupdaterec itemforupdate%ROWTYPE;			
BEGIN
				select max(order_id) into maxorderid from sales_order;
				temp_item_id:=itemtoupdaterec.item_id%type;
				temp_order_id:=itemtoupdaterec.order_id%type;				
				previous_customer_id:=itemtoupdaterec.customer_id%type;
				previous_ship_date:=itemtoupdaterec.ship_date%type;
				previous_order_id:=itemtoupdaterec.order_id%type;
FOR itemtoupdaterec in itemforupdate
	loop
		IF itemtoupdate%NOTFOUND
		then
			exit;
			ELSIF order_id = previous_order_id 
				OR 
					(customer_id=previous_customer_id 
						and ship_date=previous_ship_date 	
						and st.countt>1)
				temp_item:=temp_item_id+1;
				ELSE
				temp_order_id:=temp_order_id+1;
				temp_item_id:=1;
				exit;
		end if;
		update ph2_item
		set item_id=temp_item_id, order_id=temp_order_id
		where current of itemtoupdate;
		commit;	
		temp_customer_id=itemtoupdaterec.previous_customer_id;
		temp_ship_date=itemtoupdaterec.previous_ship_date;
		temp_order_id=itemtoupdaterec.previous_order_id;
		end loop;
end;


Which is generating the following error:

SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   33   34   35   36   37   38   39   40   41   42   43
 44  /
                                temp_item:=temp_item_id+1;
                                *
ERROR at line 26:
ORA-06550: line 26, column 5:
PLS-00103: Encountered the symbol "TEMP_ITEM" when expecting one of the
following:
* & = - + < / > at in is mod remainder not rem then
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between overlaps || multiset year DAY_ member
SUBMULTISET_
The symbol "then" was substituted for "TEMP_ITEM" to continue.
ORA-06550: line 36, column 19:
PLS-00103: Encountered the symbol "=" when expecting one of the following
:= . ( @ % ;
ORA-06550: line 37, column 3:
PLS-00103: Encountered the symbol "TEMP_SHIP_DATE"
ORA-06550: line 38, column 3:
PLS-00103: Encountered the symbol "TEMP_ORDER_ID"
ORA-06550: line 38, column 50:
PLS-00103: Encountered the symbol ";" when expecting one of the following
. ( * @ % & - + / at mod remainder rem return returning
<an exponent (**)> and or || multiset


I have no idea what the problem is, I have been sitting here for hours!! Any help would be greatly appreciated.
Re: A Syntax issue, whats wrong? [message #282623 is a reply to message #282621] Thu, 22 November 2007 11:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Looks like a missing "then" right before the error.
Re: A Syntax issue, whats wrong? [message #282624 is a reply to message #282623] Thu, 22 November 2007 11:06 Go to previous messageGo to next message
rorey_breaker
Messages: 5
Registered: October 2007
Junior Member
My word that was a quick reply, I had just noticed and was coming back to delete the post.

SILLY ME. Thanks for your time!
Re: A Syntax issue, whats wrong? [message #282675 is a reply to message #282621] Thu, 22 November 2007 23:49 Go to previous message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

hi,

as per my thinking you missed "then" key word whcih is presented in the following code in bold ,
thus your correct code is :-

DECLARE
  maxorderid number(4);

  CURSOR itemtoupdate IS
  SELECT i.order_id, i.customer_id, i.ship_date, st.countt
  FROM AA i, BB st
  where i.customer_id=st.customer_id
  order by   order_date, customer_id, ship_date, item_id
  FOR UPDATE OF i.order_id, i.item_id NOWAIT;

  itemforupdaterec itemforupdate%ROWTYPE;

BEGIN
	select max(order_id)
        into maxorderid
        from sales_order;

	temp_item_id:=itemtoupdaterec.item_id%type;
	temp_order_id:=itemtoupdaterec.order_id%type;				
	previous_customer_id:=itemtoupdaterec.customer_id%type;
	previous_ship_date:=itemtoupdaterec.ship_date%type;
	previous_order_id:=itemtoupdaterec.order_id%type;

FOR itemtoupdaterec in itemforupdate
loop
IF
  itemtoupdate%NOTFOUND	then
  exit;
ELSIF
  order_id = previous_order_id
  OR 
  (customer_id=previous_customer_id 
  and ship_date=previous_ship_date
  and st.countt>1) [B]then[/B]
  temp_item:=temp_item_id+1;
ELSE
  temp_order_id:=temp_order_id+1;
  temp_item_id:=1;
  exit;
end if;

update AA
set item_id=temp_item_id, order_id=temp_order_id
where current of itemtoupdate;
commit;	

temp_customer_id=itemtoupdaterec.previous_customer_id;
temp_ship_date=itemtoupdaterec.previous_ship_date;
temp_order_id=itemtoupdaterec.previous_order_id;

end loop;
end;
Previous Topic: Old Oracle standard and ANSI standard help
Next Topic: Use of Case Statement
Goto Forum:
  


Current Time: Wed Dec 07 10:49:13 CST 2016

Total time taken to generate the page: 0.10501 seconds