Hello,
I have a table which has a column called 'rank' of datatype NUMBER. I basically want to perform a top-n analysis and insert the ROWNUM along with other columns in the table.
TABLE1 definition:
CREATE TABLE
(
col1 VARCHAR2(10),
col2 NUMBER,
col3 NUMBER,
rank NUMBER
);
INSERT INTO table1
SELECT col1, col2, col3, rownum
FROM (
SELECT col1, col2, col3
FROM table2
ORDER BY col3 DESC
)
WHERE rownum <= 5;
I get an error, ORA-01722: invalid number. I think the error is because ROWNUM pseudocolumn is not of NUMBER datatype, so I tried to cast it like CAST(ROWNUM AS NUMBER), but it does not work, same error. Can can one please shed some light on how to convert ROWNUM to a number or if the issue is something else.