SELECT *, ROW_NUMBER() OVER in Oracle

Question

Given a working example in T-SQL:

SELECT *, ROW_NUMBER() OVER (ORDER BY name ) as row_id
 FROM 
[schemaName].[Zoo]

If I keep * selector it throws following error ORA-00923: FROM keyword not found where expected.

Is there a way to keep * selector, and make it work in oracle pl/sql? (currently I could achieve wanted result only by mentioning all column names)

What's this got to do with plsql? If nothing, please remove from tag list. Thanks! — , Jul 25 '16 at 12:05

score 13 · Accepted Answer · edited Jul 25 '16 at 12:27

13

Try this:

SELECT a.*, ROW_NUMBER() OVER (ORDER BY name ) as row_id FROM schemaName.Zoo a

Here a is alias for table schemaName.Zoo. This will generate all columns from the original table, with row_id column added at the end.

edited Jul 25 '16 at 12:27

answered Jul 25 '16 at 11:59

Sanjay Radadiya

perfect, it does the job! Thank you – meta4 Jul 25 '16 at 12:01
1

Of course, with Oracle you'll need to get rid of the square braces. – Bob Jarvis - Слава Україні Jul 25 '16 at 12:02
problem was not in squares, it was in alias, Microsoft sql supported * without alias, Oracle as I see does not – meta4 Jul 25 '16 at 12:04
1

@meta4: that's true, but with square braces in the query it won't execute in Oracle - or at least it won't execute in Oracle 11.1, which is what I happen to have handy for testing right now. – Bob Jarvis - Слава Україні Jul 25 '16 at 12:04
ohh yes I assumed to translate the query – meta4 Jul 25 '16 at 12:05

score 3 · Answer 2 · answered Jul 25 '16 at 11:59

3

Use SELECT t.*, ROW_NUMBER ... FROM tablename t;

answered Jul 25 '16 at 11:59

nilsman

2 Answers2