Convert second to second field to numeric or other

Hello, I have an issue where a field with second to second datatype cannot be viewed through heterogenous gateway on the Oracle side. Now I want to resolve it on the Informix side by converting the field datatype to a numeric or char. I've tried everything I could think off to no avail. Any ideas how to do this? Thanks, Pierre

You cannot cast a DATETIME to a numeric type, you would have to cast it to a character first then case the character string to an integer: ((st_seconds_only::char)::int) Note: > create table dumb_dt( one datetime second to second); Table created. > insert into dumb_dt values ('37'); 1 row(s) inserted. > select * from dumb_dt> ; one 37 1 row(s) retrieved. > select one + 10 from dumb_dt; 1263: A field in a datetime or interval value is incorrect or an illegaloperation specified on datetime field. Error in line 1Near character position 27 > select (one::char(4))::int + 10 from dumb_dt; (expression) 47 1 row(s) retrieved. > Use a double cast like that to define a VIEW and import the view into Oracle. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ 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 Wed, Apr 13, 2011 at 3:44 PM, PIERRE ROUSSIN < pierre.roussin@espacejeux.com> wrote: > Hello, > I have an issue where a field with second to second datatype cannot be > viewed > through heterogenous gateway on the Oracle side. Now I want to resolve it > on > the Informix side by converting the field datatype to a numeric or char. > I've > tried everything I could think off to no avail. > Any ideas how to do this? > > Thanks, Pierre > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3071c9dcd3c53d04a0d21c4a

If you are wanting to do this regularly then you could/should create your cast -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Wednesday, April 13, 2011 2:50 PM To: ids@iiug.org Subject: Re: Convert second to second field to numeric .... [23405] You cannot cast a DATETIME to a numeric type, you would have to cast it to a character first then case the character string to an integer: ((st_seconds_only::char)::int) Note: > create table dumb_dt( one datetime second to second); Table created. > insert into dumb_dt values ('37'); 1 row(s) inserted. > select * from dumb_dt> ; one 37 1 row(s) retrieved. > select one + 10 from dumb_dt; 1263: A field in a datetime or interval value is incorrect or an illegaloperation specified on datetime field. Error in line 1Near character position 27 > select (one::char(4))::int + 10 from dumb_dt; (expression) 47 1 row(s) retrieved. > Use a double cast like that to define a VIEW and import the view into Oracle. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ 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 Wed, Apr 13, 2011 at 3:44 PM, PIERRE ROUSSIN < pierre.roussin@espacejeux.com> wrote: > Hello, > I have an issue where a field with second to second datatype cannot be > viewed > through heterogenous gateway on the Oracle side. Now I want to resolve it > on > the Informix side by converting the field datatype to a numeric or char. > I've > tried everything I could think off to no avail. > Any ideas how to do this? > > Thanks, Pierre > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3071c9dcd3c53d04a0d21c4a **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. _____ avast! Antivirus <http://www.avast.com>; : Outbound message clean. Virus Database (VPS): 110413-1, 04/13/2011 Tested on: 4/13/2011 2:53:53 PM avast! - copyright (c) 1988-2011 ALWIL Software.