Row Number function in PL/SQL

Question

I am trying to fetch the specific number of records from a table using cte and Row number function. Whenever I run the query I am getting an error regarding the position of "FROM" clause in the query. Please suggest the reason behind this error as I am new to Sql.

Error:

ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" Cause:
Action: Error at Line: 3 Column: 10

code:

WITH cte2 AS (
    SELECT
        *, row_number() over (order by a.v_prt_nbr_spr) as rw_number
    FROM
        cf_active_pn_master a
        INNER JOIN cf_conn_support b ON a.obj_id = b.obj_id
    WHERE
        a.v_prt_nbr_spr LIKE 'HQCD%'
)
SELECT *
FROM cte2
where rw_number between 5000 and 10000;

Answer 1

To use * with anything else added to your SELECT list, you have to alias the *:

WITH cte2 AS (
    SELECT
        a.*, row_number() over (order by a.v_prt_nbr_spr) as rw_number
    FROM cf_active_pn_master a . . .

If you want both a and b columns, you have to list at least one of them out fully because a.*,b.* would return duplicate obj_id columns and that's not permitted in a query block that is a view for another block. So:

WITH cte2 AS (
    SELECT
        a.*, 
        b.col1,
        b.col2,
        b.col3,
        row_number() over (order by a.v_prt_nbr_spr) as rw_number
    FROM
        cf_active_pn_master a
        INNER JOIN cf_conn_support b ON a.obj_id = b.obj_id 
        . . .