rowid equivalent

[Former Member]

Hi

Is there an equivalent of a row address id, independent of any column based id ? In Oracle the rowid can be used to perform a select or can even be returned from an insert for example:

SQL> desc test1            
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                          NUMBER(6)
 Y                          VARCHAR2(20)

SQL> select rowid, x,y from test1 where x =1;

ROWID               X Y
------------------ ---------- --------------------
AAAWGQAAEAAAAIeAAA      1 yyy

SQL> select x,y from test1 where rowid = 'AAAWGQAAEAAAAIeAAA';

     X Y
---------- --------------------
     1 yyy

SQL> variable r varchar2(30)
SQL> insert into test1 values (99,'blue') returning rowid into :r;

1 row created.

SQL> select x,y from test1 where rowid = :r;

     X Y
---------- --------------------
    99 blue

[VolkerBarth · Accepted solution]

SQL Anywhere offers the ROWID function, which expects the name of the according base table or correlation name as argument.

Note that the returned value may be different when the table is reorganized, and values may be reused when a row has been deleted. (But this seems to affect Oracle's ROWID, as well.)

Personally, I'd always prefer primary keys to identify rows.

[Former Member · Accepted solution]

that is what I am after, I simply need it for a select straight after an INSERT. So can one do an

INSERT .. RETURNING ROWID() INTO VARIABLE

?

[VolkerBarth · Accepted solution]

SQL Anywhere does not offer INSERT with a RETURNING clause. However, you can use a “DML-derived-query“ to access freshly inserted/updated values. ROWID() requires a oarticular solution:

create table T_Test (
   pk    int default autoincrement,
   col2  varchar(100) not null,
   primary key (pk)
);
-- just a simple insert
insert T_Test(col2) values('test2');
-- the usual way to get a default autoincrement value AFTER the insert
select @@identity;
-- alternatively use a "dml-derived-table" (here over an INSERT) to get the freshly inserted values
select F.pk
   from (insert T_Test(col2) values('test2')) referencing (final as F);
-- NOTE, the same approach does not work for ROWID() as that requires the name of a base (or temporary/proxy) table as argument, not a logical view
-- like "F" or "FINAL" in this test, and therefore raises a -1113 SQLCODE:
select F.pk, ROWID(F)
   from (insert T_Test(col2) values('test2')) referencing (final as F);
-- Therefore use a join to the base table itself to apply the ROWID() function to the latter
select F.pk, rowid(T)
   from (insert T_Test(col2) values('test2')) referencing (final as F)
      inner join T_Test T on F.pk = T.pk;