Problem with ROWNUM

Question

I have a view. one of the view's column contains NVL function (like nvl(col1,col2) colA). and if i made query on this i will display some records

Ex: select calA from view1;

and if i use the same query as inline view and i use rownum in where clause it displays nothing

Ex: Select * from (select calA from view1) where rownum <10;

I am getting the same records if I

• try without rownum in where clause
• remove nvl from view

Answer 1

Looks like you have some other problem.

I tried the following, and your SELECT worked fine (returning 9 rows):

CREATE TABLE test_data (col1 INT, col2 INT);

INSERT INTO test_data (
  SELECT CASE WHEN MOD(ROWNUM,2) = 1 THEN
                NULL
              ELSE
                ROWNUM/2
              END col1,
         ROWNUM col2
  FROM dual
  CONNECT BY ROWNUM <= 20
);

CREATE OR REPLACE VIEW view1 AS (
  SELECT NVL(col1, col2) calA FROM test_data
);

SELECT * FROM (SELECT calA FROM view1) WHERE ROWNUM <10;

Could you please post the source of your view, the design of your table and some more information about your data?

Answer 2

This should not cause a problem:

where rownum < 10

But this would cause no rows to be returned, due to the way ROWNUM is generated:

where rownum > 10
          -- ^ Greater than