Peng Tuck Kwok wrote: > I was looking into the mailling list archives about obtaining the > current value of a given sequence and stumbled on this posting: > > http://listserv.sap.com/pipermail/sapdb.general/2000-November/ 005922.html > > I tried it out on my own database , I seemed to get four > values for the > current sequence value. The values are identical and fortunately a > select distinct will fix this. Should a select from the > sequence_name.currval return more than one row ? > > > Here's my select statement : > > SELECT MSGIDSEQ.CURRVAL FROM MESSAGEQUEUE > > Here's my table structure : > > CREATE TABLE "SMSBILL"."MESSAGEQUEUE" > ( > "MSGID" Integer, > "MSGBODY" Varchar (160) ASCII NOT NULL, > "MSGOWNER" Varchar (50) ASCII NOT NULL, > "MSGTIMESTAMP" Timestamp NOT NULL, > "DLR" Integer, > "RECIPIENT" Varchar (20) ASCII NOT NULL, > "SENDER" Varchar (20) ASCII NOT NULL, > PRIMARY KEY ("MSGID") > ) > > And finally here's my sequence: > > CREATE SEQUENCE "MSGIDSEQ" INCREMENT BY 1 MINVALUE 1 MAXVALUE > 99999999999999999999999999999999999999 NOCYCLE CACHE 100 ORDER > > > Comments and suggestions would be nice, thanks in advance.
Mhm, may I assume that your table messagequeue has exactly 4 rows in it? What would you expect if the select would look like this: select 1 from messagequeue I hope, you will, as all others familiar with SQL expect as many rows in the result as are in the table you select from (messagequeue). And all are forced to produce the result 1. Ok, if you will agree, that this is correct, why do you expect a different behaviour for the (at that moment) constant value MSGIDSEQ.CURRVAL ? And as was written in the mail you pointed to, select testseq.currval from dual should be used, where DUAL is exactly this one special table with exactly one (unimportant) column and exactly one row in it, which should be used to ask for (at that moment) constant values like user, date or <sequence name>.CURRVAL.. Elke SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
