Using ROWID in a WHERE clause
my team is trying to select a value from a table using the rowid as the selection criterion
e.g. select column_name from table_name where rowid > 'AAA112BBBCCC12A'
this query does not appear to return accurate results e.g. it return rows instead of two. my questions are:
1. is this a legitimate approach?
2. should we convert the row id to varchar2? if so how should this be done?
shouldnt be using rowid at all like that. You can say rowid >, it just doesnt make sense
stop using rowid altogether and use the columns you actually want to query on
What is the question that the SQL is supposed to answer? It is certainly returning accurate results, but your team is probably "asking the wrong question".
You'll see lots of references to rowid, but hardly anything as to the specific value of rowid. Two common uses/places are:
1) finding/deleting duplicates
2) PL/SQL, where rowid is selected into a variable, and then the rowid is used in the where clause instead of matching column this to that several times over. Use of rowid in this manner is similar to using "where current of" in a cursor (where the cursor was created with "for update").
In either situation, the actual value is never coded (no 'AAAAA...123').
Click Here to Expand Forum to Full Width