Syntax error problem

[Stephany Young]

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,
f21,
f22,
f23,
f24,
f25
from
tgetspnamattribs
return
f01,
f02,
f03,
f04,
f05,
f06,
f07,
f08,
f09,
f10,
f11,
f12,
f13,
f14,
f15,
f16,
f17,
f18,
f19,
f20,
f21,
f22,
f23,
f24,
f25
with resume;
end foreach

end function;
---------------------------------

[Stephany Young]

Thank you very much.

"Tsutomu Ogiwara" <tsutomu_ogiwara@hotmail.com> wrote in message
news:bf59cl$6st$1@terabinaries.xmission.com...

>
> Hi Stephay.
>
> I tested your function.
>
> An error occurs as below.
>
> insert ...
> select ...
> null,
> null,
> null
>
> Try as below.
> 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
> ) 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
> 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;
>
> --
> Tsutomu Ogiwara from Tokyo Japan.
> ICQ#:168106592
>
>
>
>
>

> >From: "Stephany Young" <stephany@sysoft.co.nz>
> >Reply-To: "Stephany Young" <stephany@sysoft.co.nz>
> >To: [email]informix-list@iiug.org[/email]
> >Subject: Syntax error problem
> >Date: Thu, 17 Jul 2003 13:03:14 +1200
> >
> >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,
> > f21,
> > f22,
> > f23,
> > f24,
> > f25
> > from
> > tgetspnamattribs
> > return
> > f01,
> > f02,
> > f03,
> > f04,
> > f05,
> > f06,
> > f07,
> > f08,
> > f09,
> > f10,
> > f11,
> > f12,
> > f13,
> > f14,
> > f15,
> > f16,
> > f17,
> > f18,
> > f19,
> > f20,
> > f21,
> > f22,
> > f23,
> > f24,
> > f25
> > with resume;
> > end foreach
> >
> >end function;
> >---------------------------------
> >
> >

>
> __________________________________________________ _______________
> STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
> [url]http://join.msn.com/?page=features/junkmail[/url]
>
> sending to informix-list