Syntax error problem
The following is the source of a a function I am trying to define in
Informix IDS 9.3 (Windows 2000 Server).
As far as I can figure, the syntax is correct, however when I try to execute
it, I get a syntax error.
Unfortunately there is no other information except that there is a syntax
error.
The intention of the function is to:
create a temporary table
populate the temporary table
massage the data in the temporary table that fits a certain criteria
return the contents of the temporary table
Can anyone tell me where I am holding my mouth wrong please?
As an extra, is there a way to get the column names as well as the values in
the
ADO recordset that is returned by this function (or a function such as
this)?
---------------------------------
create function getspnamattribs (
phdrid int
) returning
varchar(30),
varchar(255),
varchar(255),
int,
int,
varchar(50),
int,
int,
smallint,
smallint,
smallint,
smallint,
smallint,
smallint,
int,
varchar(2),
varchar(50),
int,
int,
int,
varchar(10),
int,
int,
varchar(255),
varchar(255);
define detid int;
define intrid int;
define xval varchar(255);
define attribid int;
define listid int;
define lval varchar(255);
define dval varchar(255);
define f01 varchar(30);
define f02 varchar(255);
define f03 varchar(255);
define f04 int;
define f05 int;
define f06 varchar(50);
define f07 int;
define f08 int;
define f09 smallint;
define f10 smallint;
define f11 smallint;
define f12 smallint;
define f13 smallint;
define f14 smallint;
define f15 int;
define f16 varchar(2);
define f17 varchar(50);
define f18 int;
define f19 int;
define f20 int;
define f21 varchar(10);
define f22 int;
define f23 int;
define f24 varchar(255);
define f25 varchar(255);
create temp table tgetspnamattribs (
ia_attrib_desc_sh varchar(30),
data_new varchar(255),
iid_typ_att_val varchar(255),
iid_inv_det_id int,
ita_typ_att_seq int,
ia_attrib_desc_lg varchar(50),
ita_attrib_id int,
ita_typ_att_ref_id int,
ita_typ_att_mand smallint,
ita_typ_att_list smallint,
ita_typ_att_limit smallint,
ita_typ_att_maint smallint,
ita_typ_att_financ smallint,
ita_typ_att_intr smallint,
ita_intr_id int,
iu_uom_type varchar(2),
iu_uom_desc_lg varchar(50),
ia_attrib_id int,
iu_uom_id int,
iid_inv_hdr_id int,
ia_attrib_code varchar(10),
iid_typ_att_id int,
ital_list_id int,
ital_tatt_lst_val varchar(255),
ital_lst_dval varchar(255)
);
insert into tgetspnamattribs (
ia_attrib_desc_sh,
data_new,
iid_typ_att_val,
iid_inv_det_id,
ita_typ_att_seq,
ia_attrib_desc_lg,
ita_attrib_id,
ita_typ_att_ref_id,
ita_typ_att_mand,
ita_typ_att_list,
ita_typ_att_limit,
ita_typ_att_maint,
ita_typ_att_financ,
ita_typ_att_intr,
ita_intr_id,
iu_uom_type,
iu_uom_desc_lg,
ia_attrib_id,
iu_uom_id,
iid_inv_hdr_id,
ia_attrib_code,
iid_typ_att_id,
ital_list_id,
ital_tatt_lst_val,
ital_lst_dval
) select
ia_attrib_desc_sh,
iid_typ_att_val,
iid_typ_att_val,
iid_inv_det_id,
ita_typ_att_seq,
ia_attrib_desc_lg,
ita_attrib_id,
ita_typ_att_ref_id,
ita_typ_att_mand,
ita_typ_att_list,
ita_typ_att_limit,
ita_typ_att_maint,
ita_typ_att_financ,
ita_typ_att_intr,
ita_intr_id,
iu_uom_type,
iu_uom_desc_lg,
ia_attrib_id,
iu_uom_id,
iid_inv_hdr_id,
ia_attrib_code,
iid_typ_att_id,
null,
null,
null
from
inv_inv_det
left join inv_typ_att_ref on ita_typ_att_ref_id=iid_typ_att_id
left join inv_attrib_ref on ia_attrib_id=ita_attrib_id
left join inv_uom_ref on iu_uom_id=ia_attrib_uom_id
where
iid_inv_hdr_id=phdrid and
ita_typ_att_view=1;
foreach select
iid_inv_det_id,
ita_intr_id,
iid_typ_att_val,
iid_typ_att_id
into
detid,
intrid,
xval,
attribid
from
tgetspnamattribs
where
ita_typ_att_list=1 and
iid_typ_att_val is not null
if intrid=0 then
select
ital_list_id,
ital_tatt_lst_val,
ital_tatt_lst_dval
into
listid,
lval,
dval
from
inv_typ_att_list
where
ital_tatt_ref_id=attribid and
ital_tatt_lst_val=xval;
else
select
itrl_list_id,
itrl_list_val,
itrl_list_dval
into
listid,
lval,
dval
from
inv_intrinsic_list
where
itrl_intr_id=intrid and
itrl_list_val=xval;
end if
update
tgetspnamattribs
set
ital_list_id=listid,
ital_tatt_lst_val=lval,
ital_lst_dval=dval
where
iid_inv_det_id=detid;
end foreach
foreach select
ia_attrib_desc_sh,
data_new,
iid_typ_att_val,
iid_inv_det_id,
ita_typ_att_seq,
ia_attrib_desc_lg,
ita_attrib_id,
ita_typ_att_ref_id,
ita_typ_att_mand,
ita_typ_att_list,
ita_typ_att_limit,
ita_typ_att_maint,
ita_typ_att_financ,
ita_typ_att_intr,
ita_intr_id,
iu_uom_type,
iu_uom_desc_lg,
ia_attrib_id,
iu_uom_id,
iid_inv_hdr_id,
ia_attrib_code,
iid_typ_att_id,
ital_list_id,
ital_tatt_lst_val,
ital_lst_dval
into
f01,
f02,
f03,
f04,
f05,
f06,
f07,
f08,
f09,
f10,
f11,
f12,
f13,
f14,
f15,
f16,
f17,
f18,
f19,
f20,