Re: insert current + 3 units second

Dbaccess complains on a "+" sign of the statement. But there is no problem with the syntax at all if I use update instead of insert. e.g. UPDATE sb_cache set tstamp = CURRENT + 5 UNITS SECOND works perfectly. thanks julia "Parker, Jack" wrote: > Try > > Current hour to second + 3 units second. > > cheers > j. > > > -----Original Message----- > > From: Ousmanov (Julia) [mailto:julia@farelogix.com] > > Sent: Tuesday, November 21, 2000 12:02 PM > > To: informix users > > Subject: insert current + 3 units second > > > > > > Hi all, > > > > I have some troubles writing possibly very simple statement: > > > > INSERT INTO sb_cache (cmd, tstamp) VALUES("test cmd", CURRENT> > + 5 UNITS > > SECOND); > > > > table sb_cache: > > cmd - char32 > > tstamp - datetime year to fraction(3) > > > > We use Informix 7.22 and 9.21 and I need this statement to > > work on both > > of them. > > > > Thanks for your help > > julia > >

"Ousmanov (Julia)" wrote: > > Dbaccess complains on a "+" sign of the statement. > But there is no problem with the syntax at all if I use update instead > of insert. > e.g. UPDATE sb_cache set tstamp = CURRENT + 5 UNITS SECOND works > perfectly. The trouble is that the values in the VALUES list of an INSERT statement must be simple values and are not allowed to be expressions in general. This is a (bad) hangover from the earliest days of SQL (say the mid-80s). One primary use of the VALUES list was in ESQL/C (or ESQL PL/I) with a question-mark place holder for each value, and the only way to supply the value was to give the name of a host variable as the value -- not an expression. However, it isn't quite that simple; there are some expressions you can use in a VALUES list, such as MDY(12,31,1899). I've not gone poking around the manuals to see how the subset of valid expressions is documented. The UPDATE statement accepts full expressions, hence the UPDATE works. How to work around this? You can use a stored procedure in the VALUES list, as shown: SQL[1677]: create procedure inctime(i integer) returning datetime year to second; > return current year to second + i units second; > end procedure; SQL[1678]: create temp table t1 (dt datetime year to second not null); SQL[1679]: insert into t1 values(inctime(3)); SQL[1680]: select * from t1; 2000-11-21 16:37:55 SQL[1681]: Have fun... > "Parker, Jack" wrote: > > Try > > > > Current hour to second + 3 units second. > > > > > -----Original Message----- > > > From: Ousmanov (Julia) [mailto:julia@farelogix.com] > > > Sent: Tuesday, November 21, 2000 12:02 PM > > > To: informix users > > > Subject: insert current + 3 units second > > > > > > I have some troubles writing possibly very simple statement: > > > > > > INSERT INTO sb_cache (cmd, tstamp) VALUES("test cmd", CURRENT> > > + 5 UNITS SECOND); > > > > > > table sb_cache: > > > cmd - char32 > > > tstamp - datetime year to fraction(3) > > > > > > We use Informix 7.22 and 9.21 and I need this statement to > > > work on both of them. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"