RE: upgrade (to IDS9.2) problem
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BF821B.85DAB3F6
Content-Type: text/plain
Hello,
I am trying to see if I can get some help.
I am come acrossing a SQL error after I upgrade IDS7.3x to IDS9.2.
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 = int_inter;
let iint = cintv;
return iint;
end procedure;
and I have a SQL statement :
update tos_oldstate
set os_partial = 2
where os_timestamp < (datetime( 2000-03-07 ) year to day + 1
units day )
and (os_timestamp + inttointv(os_duration)) >= (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 failed 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. And "select * from v_myview" works well !!!
----------------------------------------------------------------------------
---------------
> echo "select * from syscolumns where tabid=114" | 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 = 2
where os_timestamp < (datetime( 2000-03-07 ) year to day + 1
units day )
and extend((os_timestamp + inttointv(os_duration)), year to
second)
>= (datetime( 2000-03-07 ) year to day + 1 units
day);
It works !!! Then where is the problem ?
"Check every SQL statement to see if it works in new version and
find a workaround if no before upgrade" ? I do not know how much time
it will take for me to do this on our system.
I hope you can give me some advices to do this check and can save me much
time ?
thanks.
Harry
------_=_NextPart_001_01BF821B.85DAB3F6
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3DUS-ASCII">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: upgrade (to IDS9.2) problem</TITLE>
</HEAD>
<BODY>
<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Hello,</FONT>
</P>
<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">I am trying to see =
if I can get some help.</FONT>
<BR><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">I am come acrossing =
a SQL error after I upgrade IDS7.3x to IDS9.2.</FONT>
</P>
<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">I have a =
table TOS_OLDSTATE:</FONT>
</P>
<P><FONT COLOR=3D"#0000FF" SIZE=3D2 =
FACE=3D"Arial"> Column =
name =
Type &n=
bsp; &n=
bsp; =
Nulls</FONT>
<BR><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial"> </FONT>
<BR><FONT COLOR=3D"#0000FF" SIZE=3D2 =
FACE=3D"Arial"> =
os_timestamp datetime =
year to second no</FONT>
<BR><FONT COLOR=3D"#0000FF" SIZE=3D2 =
FACE=3D"Arial"> =
os_eventid &n=
bsp; =
integer  =
;  =
; no</FONT>
<BR><FONT COLOR=3D"#0000FF" SIZE=3D2 =
FACE=3D"Arial"> =
os_netentityid =
integer  =
;  =
; no</FONT>
<BR><FONT COLOR=3D"#0000FF" SIZE=3D2 =
FACE=3D"Arial"> =
os_netentitytype =
smallint &nbs=
p; &nbs=
p; no</FONT>
<BR><FONT COLOR=3D"#0000FF" SIZE=3D2 =
FACE=3D"Arial"> =
os_dura