Syntax error in function
A user on IDS 11.50 (Linux) got "syntax error 201" creating an SPL function adddate() that selected date_value + INTERVAL (dayval) DAY TO DAY "from dual". Replies pointed out two faults: the parameter declaration was missing a type keyword (should be INTERVAL/DATETIME DAY TO DAY), and there is no 'dual' table in Informix — it exists as sysmaster:sysdual (create a synonym, or reference it directly). Art Kagel supplied working rewrites using LET j = date_value + dayval, or passing an INT and using LET j = date_value + dayval UNITS DAY.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Dear All I am trying to create a function adddate() which will add the given days in given date but when I try to execute this function then it gives error "syntax error 201" Platform Detail: Database- Informix 11.50 OS - Linux SuSe CREATE FUNCTION "informix".adddate (date_value datetime year to day,dayval DAY TO DAY) RETURNING datetime year to day; DEFINE j datetime year to day; select date_value + INTERVAL (dayval) DAY TO DAY into j from dual; RETURN j; END FUNCTION; Regards Nierjesh --005045016b594ccacd0484099faf
When did informix implement a dummy table named "dual"?.. I thought that was an Oracle feature. Well, if "dual" is not supported, but you created table "dual", then you have a syntax error somewhere else.
Try this: CREATE FUNCTION "informix".adddate(date_value datetime year to day, dayval INTERVAL DAY TO DAY) RETURNING datetime year to day; DEFINE j datetime year to day; LET j = date_value + dayval; RETURN j; END FUNCTION; Or, if you'd rather pass in a integer number of days rather than an INTERVAL: CREATE FUNCTION "informix".adddate(date_value datetime year to day, dayval INT) RETURNING datetime year to day; DEFINE j datetime year to day; LET j = date_value + dayval UNITS DAY; RETURN j; END FUNCTION; Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Apr 12, 2010 at 8:52 AM, nierjesh kumar <nierjeshkumar@gmail.com>wrote: > Dear All > > I am trying to create a function adddate() which will add the given days in > given date but when I try to execute this function then it gives error > "syntax error 201" > > Platform Detail: > Database- Informix 11.50 > OS - Linux SuSe > > CREATE FUNCTION "informix".adddate > > (date_value datetime year to day,dayval DAY TO DAY) > > RETURNING datetime year to day; > > DEFINE j datetime year to day; > > select date_value + INTERVAL (dayval) DAY TO DAY into j from dual; > > RETURN j; > > END FUNCTION; > > Regards > Nierjesh > > --005045016b594ccacd0484099faf > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e68de9e87ff2b604840a6c63
The 'dual' table concept was added to IDS in 9.40 IB, but it only exists in the sysmaster database and it's actual name is 'sysdual'. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Apr 12, 2010 at 9:38 AM, FRANK@ FRANKCOMPUTER.COM < frank@frankcomputer.com> wrote: > When did informix implement a dummy table named "dual"?.. I thought that > was > an Oracle feature. Well, if "dual" is not supported, but you created table > "dual", then you have a syntax error somewhere else. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e68de9e89c9b7104840a853c
Missing DATETIME in (..... , dayval DATETIME DAY TO DAY) Regards, Srini "nierjesh kumar" <nierjeshkumar@gm ail.com> To Sent by: ids@iiug.org ids-bounces@iiug. cc org Subject Syntax error in function [19624] 12/04/2010 18:22 Please respond to ids@iiug.org Dear All I am trying to create a function adddate() which will add the given days in given date but when I try to execute this function then it gives error "syntax error 201" Platform Detail: Database- Informix 11.50 OS - Linux SuSe CREATE FUNCTION "informix".adddate (date_value datetime year to day,dayval DAY TO DAY) RETURNING datetime year to day; DEFINE j datetime year to day; select date_value + INTERVAL (dayval) DAY TO DAY into j from dual; RETURN j; END FUNCTION; Regards Nierjesh --005045016b594ccacd0484099faf ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
11.10, if I remember correctly. = From: "FRANK@ FRANKCOMPUTER.COM" <frank@frankcomputer.com> = = To: ids@iiug.org = = Date: 04/12/2010 08:40 AM = = Subject: Re: Syntax error in function [19625] = = Sent by: ids-bounces@iiug.org = = When did informix implement a dummy table named "dual"?.. I thought tha= t was an Oracle feature. Well, if "dual" is not supported, but you created ta= ble "dual", then you have a syntax error somewhere else. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
If you want to use a dual table in version 11 then just do one of the
following.
1. In your current database run the following:
create synonym dual for sysmaster:sysdual
2. instead of referencing dual reference sysmaster:sysdual
Being that this table is an in memory pseudo table it is faster
than querying a real table which act like the dual table.
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 04/12/2010 05:52:51 AM:
> [image removed]
>
> Syntax error in function [19624]
>
> nierjesh kumar
>
> to:
>
> ids
>
> 04/12/2010 05:53 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Dear All
>
> I am trying to create a function adddate() which will add the given days
in
> given date but when I try to execute this function then it gives error
> "syntax error 201"
>
> Platform Detail:
> Database- Informix 11.50
> OS - Linux SuSe
>
> CREATE FUNCTION "informix".adddate
>
> (date_value datetime year to day,dayval DAY TO DAY)
>
> RETURNING datetime year to day;
>
> DEFINE j datetime year to day;
>
> select date_value + INTERVAL (dayval) DAY TO DAY into j from dual;
>
> RETURN j;
>
> END FUNCTION;
>
> Regards
> Nierjesh
>
> --005045016b594ccacd0484099faf
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>