Re: Syntax error problem
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: informix-list@iiug.org
>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_i
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 onita_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: informix-list@iiug.org
> >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,> >