August 22nd, 2013, 01:08 PM
Join Date: Oct 2003
The use of connect by does not make any sense here, but as far as I can tell, the following should do the same thing:
select rn as table_no,
dept_code as dept_code
from (select dept_code,
row_number() over () as rn,
from t_dept t
and t.dept_code= 1
where rn <= no_tables;
As that statement will also work with Oracle you can check if it really does the same thing by comparing it with the old statement.
And the next time please format your SQL code using [code] tags.
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
Tips on how to ask better questions: