Need help with to_number routine
A user upgrading from IDS 10 to 11.50 hit "Routine (to_number) ambiguous - more than one routine resolves to given signature" because 11.50 ships a built-in to_number() (one of several Oracle-compatibility functions) that clashed with their own ported UDR. Dropping their function then failed with a conversion error, since subtracting two DATETIMEs yields an INTERVAL. John Miller and Art Kagel advised either defining a to_number() taking an INTERVAL, or casting explicitly, e.g. (...)::INTERVAL DAY TO DAY::char(5)::integer (or ::char(10)::decimal), and noted sysmaster:sysdual can replace a dual table. The poster confirmed it worked.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi Everyone, I have 2 informix DB, one is running IDS 10.0 FC9 and the other is running IDS 11.5 When I tried to execute the below mentioned query in IDS 10, it runs with no problem. But when I execute this query in IDS 11.5, I get the error "Routine (to_number) ambiguous - more than one routine resolves to given signature." Query: SELECT to_number(extend('2010-08-11',year to day) - extend('2010-08-06',year to day)) from dual We actually have created a to_number routine in IDS 10 and we ported it over when we upgraded to IDS 11.5 Anyone can tell me what is wrong with IDS 11.5? I read up some documentation that in IDS 11.5, to_number has become a keyword, is this the cause of the error?
to_number() is already a built in function that exists in version 11.
There are
many functions that have been added and/or improved in version 11, see
the list below.
In addition there is an in memory table call sysdual which you can query
and is faster than making a real table called dual. You can do a
distributed access to sysdual or make synonym
Example:
select * from sysmaster:sysdualExample of a synonym
create synonym dual for sysmaster:sysdual
* ADD_MONTHS()
* ASCII()
* BITAND()
* BITANDNOT()
* BITNOT()
* BITOR()
* BITXOR()
* CEIL()
* FLOOR()
* FORMAT_UNITS()
* LAST_DAY()
* LTRIM()
* MONTHS_BETWEEN ()
* NEXT_DAY ()
* NULLIF()
* POWER()
* ROUND()
* RTRIM()
* SYSDATE()
* TO_CHAR()
* TO_NUMBER()
* TRUNC()
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 08/12/2010 12:24:15 AM:
> [image removed]
>
> Need help with to_number routine [20859]
>
> RON TAY
>
> to:
>
> ids
>
> 08/12/2010 12:24 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Hi Everyone,
>
> I have 2 informix DB, one is running IDS 10.0 FC9 and the other is
> running IDS
> 11.5
>
> When I tried to execute the below mentioned query in IDS 10, it runs with
no
> problem. But when I execute this query in IDS 11.5, I get the error
"Routine
> (to_number) ambiguous - more than one routine resolves to given
signature."
>
> Query: SELECT to_number(extend('2010-08-11',year to day) -
> extend('2010-08-06',year to day)) from dual
>
> We actually have created a to_number routine in IDS 10 and we ported it
over
> when we upgraded to IDS 11.5
>
> Anyone can tell me what is wrong with IDS 11.5?
> I read up some documentation that in IDS 11.5, to_number has become
> a keyword,
> is this the cause of the error?
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hi John, So you mean I can omit the to_number in the query as it is now a built in function in IDS 11? Actually, my original query is this: SELECT to_number(extend('2010-08-11',year to day) - extend('2010-08-06',year to day)) < 6 from dual So, if I removed the to_number, the query will become: SELECT extend('2010-08-11',year to day) - extend('2010-08-06',year to day) < 6 from dual Now, if I run this query, I get another error "It is not possible to convert between the specified types." Can you help me see what is wrong with the query?
Just to let you know here is what is happening.
to_number function that is defined will take either an integer or a
character
data type. Now when you subtract 2 datetime values the result is
an interval (not a numeric or character data type).
Option #1
Informix will allow you to create a function called to_number which accept
an
interval data type.
create function to_number(v1 interval day to day)returns integer
return v1::char(5)::integer;
end function;
SELECT
to_number((extend('2010-08-11',year to day) - extend('2010-08-06',year to
day) )) < 6
from sysmaster:sysdual;
Option #2
Use casting
There is no direct conversion to an integer, but you can go through
a character type then to an integer. If you know this value will
be always be less than 28 then you can skip a step, else you must
add a step.
Here are the steps I use:
1. Cast the interval data type to an interval of just one type of units
(say days)
(extend('2010-08-11',year to day) - extend('2010-08-06',year to
day) )::INTERVAL DAY TO DAY
2. Cast the interval days to a character
::char(5)
3. Convert the character to an integer
::integer
Full statement
SELECT
((extend('2010-08-11',year to day) - extend('2010-08-06',year to
day) )::INTERVAL DAY TO DAY)::char(5)::integer < 6
from sysmaster:sysdual
Hope this helps,
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 08/12/2010 02:39:33 AM:
> [image removed]
>
> Re: Need help with to_number routine [20861]
>
> RON TAY
>
> to:
>
> ids
>
> 08/12/2010 02:40 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Hi John,
>
> So you mean I can omit the to_number in the query as it is now a built in
> function in IDS 11?
>
> Actually, my original query is this:
> SELECT to_number(extend('2010-08-11',year to day) - extend
('2010-08-06',year
> to day)) < 6 from dual
>
> So, if I removed the to_number, the query will become:
> SELECT extend('2010-08-11',year to day) - extend('2010-08-06',year
> to day) < 6
> from dual
>
> Now, if I run this query, I get another error "It is not possible to
convert
> between the specified types."
>
> Can you help me see what is wrong with the query?
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Yes, I wasn't aware of it when I first posted a reply to your original post, but, it looks like 11.50 has a built-in function, to_number(), that was added for Oracle compatibility. It converts a string, or anything else that's compatible, to a number. Informix already does this automatically when it is appropriate, but to ease porting of Oracle applications, this function was added. So, look in the Guide to SQL Syntax to see if you can just use that function and drop your own, especially since it looks like the function signature is substantially the same. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Thu, Aug 12, 2010 at 3:24 AM, RON TAY <rontay79@yahoo.com.sg> wrote: > Hi Everyone, > > I have 2 informix DB, one is running IDS 10.0 FC9 and the other is running > IDS > 11.5 > > When I tried to execute the below mentioned query in IDS 10, it runs with > no > problem. But when I execute this query in IDS 11.5, I get the error > "Routine > (to_number) ambiguous - more than one routine resolves to given signature." > > Query: SELECT to_number(extend('2010-08-11',year to day) - > extend('2010-08-06',year to day)) from dual > > We actually have created a to_number routine in IDS 10 and we ported it > over > when we upgraded to IDS 11.5 > > Anyone can tell me what is wrong with IDS 11.5? > I read up some documentation that in IDS 11.5, to_number has become a > keyword, > is this the cause of the error? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016361e889ce24cbb048d9fc41f
To convert an INTERVAL (the result of subtracting two DATETIME values) to a number, you have to first convert it to a character string, so: SELECT ((extend('2010-08-11',year to day) - extend('2010-08-06',year to day))::char(10))::decimal < 6 FROM sysmaster:sysdual; This returns a BOOLEAN, so 't' or 'f'. Hope that's what your intended. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Thu, Aug 12, 2010 at 5:39 AM, RON TAY <rontay79@yahoo.com.sg> wrote: > Hi John, > > So you mean I can omit the to_number in the query as it is now a built in > function in IDS 11? > > Actually, my original query is this: > SELECT to_number(extend('2010-08-11',year to day) - > extend('2010-08-06',year > to day)) < 6 from dual > > So, if I removed the to_number, the query will become: > SELECT extend('2010-08-11',year to day) - extend('2010-08-06',year to day) > < 6 > from dual > > Now, if I run this query, I get another error "It is not possible to > convert > between the specified types." > > Can you help me see what is wrong with the query? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001485e0b496f02867048d9fdeb8
Hi John and Art... Thanks a Million. It works! :)