Re: insert current + 3 units second

Hi Jonathan, Thanks for your reply, but in my case the VALUES list doesn't except SP call, it complains on the brackets used. I'm trying to do it in dbaccess. thanks julia Jonathan Leffler wrote: > "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!"