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

asked 17 Sep, 14:59

bluefrog's gravatar image

bluefrog
163112
accept rate: 0%


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.

permanent link

answered 18 Sep, 03:27

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

edited 18 Sep, 03:29

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

?

(18 Sep, 03:35) bluefrog

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;
(18 Sep, 05:29) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×6

question asked: 17 Sep, 14:59

question was seen: 39 times

last updated: 18 Sep, 05:29