Professional Web Applications Themes

Syntax error problem - Informix

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 ...

  1. #1

    Default 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,
    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 Guest

  2. #2

    Default Re: Syntax error problem

    Thank you very much.

    "Tsutomu Ogiwara" <tsutomu_ogiwarahotmail.com> wrote in message
    news:bf59cl$6st$1terabinaries.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" <stephanysysoft.co.nz>
    > >Reply-To: "Stephany Young" <stephanysysoft.co.nz>
    > >To: [email]informix-listiiug.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

    Stephany Young Guest

Similar Threads

  1. Replies: 6
    Last Post: September 2nd, 01:22 PM
  2. Syntax error
    By middletree in forum Macromedia ColdFusion
    Replies: 11
    Last Post: May 2nd, 03:10 AM
  3. error : syntax error at or near $1 for over select rows
    By vinita bansal in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 27th, 06:51 PM
  4. ASP and stored procedure problem (syntax error ?)
    By Steven Scaife in forum ASP Database
    Replies: 4
    Last Post: August 13th, 12:11 PM
  5. Replies: 2
    Last Post: August 10th, 04:27 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139