"is not scalar Variable" Error

[ashokakr]

Hi all
This my procedure, when i try to run the procedure it says
"coitem_linenumber" is not a scalar variable.

Can any one say what is the error and how i can correct it

declare
wotype CHARACTER;
id integer;
so_no ALIAS FOR $1;
_wono integer;
_wolno integer;
woisid integer;
wostdate date;
wosdate date;
woqty integer;
wopno text;
woprj integer;
woordid integer;
BEGIN
select max(wo_id) into id from wo;
wotype := 'W';
LOOP
IF id=0 THEN id=1;
ELSE id=id+1; 
END IF;
UPDATE coitem SET coitem_order_type=wotype,coitem_order_id=id 
WHERE (coitem_cohead_id=(SELECT cohead_id from cohead where(cohead.cohead_number=so_no))); 
select 
cohead_number into _wono,
coitem_linenumber into _wolno, 
coitem_itemsite_id into woisid,
coitem_lastupdated into wostdate,
coitem_scheddate into wosdate,
coitem_ordqty into woqty,
(cust_number||'-'||cust_name) into wopno, 
cohead_prj_id into woprj 
from custinfo,cohead,coitem 
where ((cust_id=cohead_cust_id) and (cohead_id=coitem_cohead_id) and (coitem_order_id=id));
select max(evntlog_order_id) into wo_ordid from eventlog where(eventlog_number=(wo_no||'-'||wo_lno));
Insert into wo values(id,wo_no,wo_lno,'R',wo_isid,wo_stdate,wo_sdate,'S',wo_ordid,wo_qty,0,'','','',0,0,wo_pno,wo_prj,1,0) ;
END LOOP;
END;
 

[michaelb]

Please read the Posting Guidelines at the top of the page and use the CODE tags for your future postings.

What datatype is your coitem_linenumber field?

[ashokakr]

ok sir,

the datatype for coitem_linenumber is integer

Please read the Posting Guidelines at the top of the page and use the CODE tags for your future postings.

What datatype is your coitem_linenumber field?

[michaelb]

Looking at your code I see this:

... ... ... ...
SELECT
cohead_number INTO _wono,
coitem_linenumber INTO _wolno,
coitem_itemsite_id INTO woisi
... ... ... ...
 

I don't think this is right, the manual on SELECT INTO says:
------------------------------------------------------------------------------------------------
The result of a SELECT command yielding multiple columns (but only one row)
can be assigned to a record variable, row-type variable, or list of scalar variables.
This is done by:
SELECT INTO target select_expressions FROM ...;
-----------------------------------------------------------------------------------------------
So it seems you need to either use a list of variables with a single INTO -

select into var1, var2, var3   field1, field2, field3  FROM  tab-name ...
 

or, considering you have a fair number of fields, it might be better just to select into var type RECORD and get what you need from there

See SELECT INTO for details