Accesing Oracle ROWNUMBER with alias name

Question

I have a Oracle Query

WITH cte AS (
SELECT

    category_id,
    category_name,
    parent_category_id,
    ltrim(sys_connect_by_path(category_name, '/'), '/') "ParentNames"       
FROM
    bg_categories
START WITH
    parent_category_id = - 1
CONNECT BY NOCYCLE
    PRIOR category_id = parent_category_id
 )
 SELECT
ROW_NUMBER() OVER (
    ORDER BY 
      bco.ID desc
  ) AS SLNO,
bco.*,
cte.*,

(
    SELECT
        COUNT(*)
    FROM
        object_document obj
    WHERE
        obj.object_id = bco.id
        AND object_type_id = 85
) AS customobjects_doc_count 
 FROM
bg_custom_objects bco
JOIN cte ON ( cte.category_id = bco.category_id )  where SLNO between 10 and 25

This query works fine with out this 'where SLNO between 10 and 25' where statement at the end , but with this it's showing "SLNO invalid identifier " error.

Pls help.

Answer 1

You need to wrap it with inline view/cte:

WITH cte AS (
  SELECT
    category_id,
    category_name,
    parent_category_id,
    ltrim(sys_connect_by_path(category_name, '/'), '/') "ParentNames"       
  FROM bg_categories
  START WITH parent_category_id = - 1
  CONNECT BY NOCYCLE
  PRIOR category_id = parent_category_id
), cte2 AS (
  SELECT
   ROW_NUMBER() OVER (ORDER BY bco.ID desc) AS SLNO
      --,bco.* -- columns should be listed explicitly
      --,cte.* -- columns should be listed explicitly
  ,(
    SELECT COUNT(*)
    FROM object_document obj
    WHERE obj.object_id = bco.id
      AND object_type_id = 85
  ) AS customobjects_doc_count 
  FROM bg_custom_objects bco
  JOIN cte ON ( cte.category_id = bco.category_id )
)
SELECT *
FROM cte2
WHERE SLNO BETWEEN 10 AND 25;

You cannot use aliases defined directly in SELECT in WHERE clause if both are on the same level because it is not visible Can't refer to column counting the ocurrences

Additionally you could not move ROW_NUMBER to WHERE either Why no windowed functions in where clauses?