A stupid SQL error in IDS9.2
This is a multi-part message in MIME format.
------=_NextPart_000_0016_01BF8766.6E809E00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello,
I am trying to find some help.
I have a table TOS_OLDSTATE:
Column name Type =
Nulls
os_timestamp datetime year to second no
os_eventid integer =
no
os_netentityid integer =
no
os_netentitytype smallint =
no
os_duration integer =
no
os_partial smallint =
no
os_adminstatetype smallint no
os_operstatetype smallint no
a stored procedure INTTOINTV:
create procedure inttointv (int_inter int)
returning interval second(8) to second; define cintv char(9);
define iint interval second(8) to second;
let cintv =3D int_inter;
let iint =3D cintv;
return iint;
end procedure;
and I have a SQL statement :
update tos_oldstate
set os_partial =3D 2
where os_timestamp < (datetime( 2000-03-07 ) year to day + =
1 units day )
and (os_timestamp + inttointv(os_duration)) >=3D
datetime( 2000-03-07 ) year to day + 1 units =
day);
If my informix server is IDS7.3x, this statement works happyly;
If my informix server is IDS9.2, this statement fails with error -1268.
Informix document says the result type of "DATETIME + INTERVAL" is
DATETIME.
I created a view :
create view v_myview ( os_timestamp, os_eventid, os_netentityid,
os_netentitytype, os_duration, =
os_partial,
os_adminstatetype, =
os_operstatetype,
additional_col =
)
as select os_timestamp, os_eventid, os_netentityid,
os_netentitytype, os_duration, os_partial,
os_adminstatetype, os_operstatetype,
(os_timestamp + inttointv(os_duration))
from tos_oldstate;
and check the datetype of ADDITIONAL_COL in the view, it is completely =
the
same as the column OS_TIMESTAMP. =20
And "select * from v_myview" works well in IDS9.2!!!
-------------------------------------------------------------------------=
---
> echo "select * from syscolumns where tabid=3D114" | dbaccess dsm
colname os_timestamp
tabid 114
colno 1
coltype 10
collength 3594
colmin
colmax
extended_id 0
colname additional_col
tabid 114
colno 9
coltype 10
collength 3594
colmin
colmax
extended_id 0
9 row(s) retrieved.
-------------------------------------------------------------------------=
---
The problem is "the previous UPDATE statement does not work on IDS9.2". =
Why ???
If I change the UPDATE statement :
update tos_oldstate
set os_partial =3D 2
where os_timestamp < (datetime( 2000-03-07 ) year to day + 1 =
units day )
and extend((os_timestamp + inttointv(os_duration)), =
year to second)
>=3D (datetime( 2000-03-07 ) year to day + 1 =
units day);
or
update tos_oldstate
set os_partial =3D 2
where os_timestamp < (datetime( 2000-03-07 ) year to day + =
1 units day )
and (os_timestamp + interval(7032) second(8) to second)
>=3D (datetime( 2000-03-07 ) year to day + 1 =
units day);
Both of them work !!!
Then where is the problem ? The stored procedure INTTOINTV ?
But is there any other better method to cast a INT column to INTERVAL ?
I hope you can give me some advices on this problem ?
thanks.
Harry
------=_NextPart_000_0016_01BF8766.6E809E00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Dwindows-1252" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2013.1310" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3Dhelvetica size=3D2>Hello,<BR><BR>I am trying to find =
some=20
help.<BR><BR><BR>I have a table =20
TOS_OLDSTATE:<BR><BR> =
Column=20
name =20
Type &nb=
sp; &nbs=
p; =20
Nulls<BR><BR> =20
os_timestamp datetime =
year to=20
second =20
no<BR> =20
os_eventid &nb=
sp; =20
integer =
&=
nbsp; =20
no<BR> =20
os_netentityid =20
integer =
&=
nbsp; =20
no<BR> =20
os_netentitytype =20
smallint  =
; =
=20
no<BR> =20
os_duration &n=
bsp; =20
integer =
&=
nbsp; =20
no<BR> =20
os_partial &nb=
sp; =20
smallint  =
; =@