Oracle 11G DB : Result from 'clob' type column in view changed while using the view in a w-CodePudding

I have the current query that i'm running in Oracle:

    WITH viewa
     AS (SELECT c.columna
         FROM   sometable c
         LEFT JOIN othertable u
         ON ( c.id = u.id )
         WHERE id= '111' 
         ORDER BY c.created_date)
SELECT columna
FROM   (SELECT rownum AS row_num,
               t.*
        FROM   viewa t)
WHERE  row_num > (SELECT CASE
                           WHEN ( Count(*) > 100 ) THEN Count(*) - 100
                           ELSE 0
                         END AS num
                  FROM   viewa)

the idea is to always get the first 100 rows. as you can see, i'm creating a view at the beginning and use it twice: in the from and in the where.

i'm doing that so i wouldn't need to fetch the first select twice and it also make the query more readable.

notice that columna is of type CLOB!! when i'm doing the same query with other column types its working! so its probably something related to the clob column

The weird think is that the results that im getting are empty values even though i have values in the DB!

when i'm removing the subselect in the where i'm getting the right result:

WITH viewa
     AS (SELECT c.columna
         FROM   sometable c
         LEFT JOIN othertable u
         ON ( c.id = u.id )
         WHERE id = '111' 
         ORDER BY c.created_date)
SELECT columna
FROM   (SELECT rownum AS row_num,
               t.*
        FROM   viewa t)
WHERE  row_num > 0

seems like Oracle is turning the values for the Clob column "columnA" into null when using the view in the where.

is someone familiar with that? know how to go around it ?

thank you

CodePudding user response：

Without sample data this is hard but I'm guessing the reason is you're depending on rownum. Use the FETCH clause instead to limit the number of rows.

WITH viewa
  AS (SELECT c.columna
        FROM sometable c
             LEFT JOIN othertable u ON ( c.id = u.id )
      -- an order by clause should go here 
         FETCH FIRST 100 ROWS ONLY)
SELECT columna
FROM   viewa

But you don't need that CTE at all, just do

SELECT c.columna
        FROM sometable c
             LEFT JOIN othertable u ON ( c.id = u.id )
      -- an order by clause should go here 
         FETCH FIRST 100 ROWS ONLY

Note that the "first" rows are not guaranteed to be a specific set of rows unless you explicitly add an ORDER BY clause.

CodePudding user response：

Since 11g does not have FETCH FIRST, you can just use rownumber as the limiting criteria. See Example at Oracle Live

select columna, created_date 
  from (  
         select c.columna, c.created_date   
           from sometable  c 
           left join othertable u 
                  on ( c.id = u.id ) 
          where c.id = '111'  
          order by c.created_date 
      )  
where rownum <= 10;