What is wrong in this "order by", "group by" sql?

Question

I have got this SQL, it yields ORA-00920 Invalid relational operator error, what is wrong and how can I fix this?

select * from my_tab where (a,b,c,t) in
 ( select a,b,c, max(t) as t from my_tab where foo<>'bar'
   group by a,b,c
   order by t
 ) and rownum between 20 and 30

(also tried rownum>20 and rownum<30)

DDL:

create table my_tab
(a number,
 b number,
 c number,
 t  date,
 foo varchar2(20),
 buzz varchar2(2000)
);

There is no index in the table.

I want to read the whole table actually, but since there are many rows, I want to process in chunks. I cannot add column to the table (for example a unique id or a counter to hold the progress).

Answer 1

The problem is that you have 3 values before the in clause and 4 columns coming from the subquery.

This code might help you.

WITH my_tab AS (

    SELECT 'a' AS a,
            'b' AS b,
            'c' AS c,
             1 AS t,
             'bar!' AS foo
    FROM dual 

)
SELECT * FROM (
    SELECT t.*, ROWNUM AS RN FROM my_tab t WHERE ('a','b','c') IN
     ( 
        SELECT a,b,c FROM ( 
            select a,b,c, max(t) as t 
                from my_tab where foo <> 'bar'
            group by a,b,c
          order by t
     )
 )
 ) 
WHERE  RN between 0 and 1

Copy and paste this code into your oracle client and see the result. I think it will work as you expect.

If you want to filter using the t value, the query below might be useful:

 WITH my_tab AS (

    SELECT 'a' AS a,
            'b' AS b,
            'c' AS c,
             1 AS t,
             'bar!' AS foo
    FROM dual 

)
SELECT * FROM (
    SELECT t.*, ROWNUM AS RN FROM my_tab t WHERE ('a','b','c', 1) IN
     ( 
        SELECT a,b,c,t FROM ( 
            select a,b,c, max(t) as t 
                from my_tab where foo <> 'bar'
            group by a,b,c
          order by t
     )
 )
 ) 
WHERE  RN between 0 and 1

This code only works using rownum between 0 and 1 (RN) since there is just one row in my_tab.

Answer 2

Either of these will work:

select * from my_tab
where (a,b,c,t) in
 ( select a,b,c,t from 
    ( select a,b,c, max(t) as t, row_number() over ( order by max(t)) rn
      from my_tab
      where foo<>'bar'
      group by a,b,c
    )
   where rn between 20 and 30
) ;

(in 12c only...)

select * from my_tab
where (a,b,c,t) in
    ( select a,b,c, max(t) as t
      from my_tab
      where foo<>'bar'
      group by a,b,c
      offset 19 rows
      fetch next 11 rows only  -- should give you rows 20 thru 30, a weird window of 11 rows
    );

Note:

I want to read the whole table actually, but since there are many rows, I want to process in chunks.

If you try to do it this way, you'll be re-reading the whole table for every chunk! If you post some more details about what you are trying to do (probably a separate question would be appropriate), someone here can probably help you avoid that with a different approach.

Answer 3

There are a couple of problems here:

1. First, the invalid comparison operator is caused by the construct (a,b,c,t) IN (...subquery...). You'll need to do an INNER JOIN of the subquery.
2. ROWNUM BETWEEN 20 AND 30 will never return any data. ROWNUM is not generated until a row is emitted from the query, so the first row emitted from the query always has ROWNUM = 1. The problem here is that you're waiting for a row with ROWNUM = 20 to be emitted - but none of the preceding rows have ever been generated. Thus, no data. The workaround for this is to pull all the rows in a subquery, then look for the appropriate rows in an outer query.

Put together, we get:

WITH cteData AS (SELECT A, B, C, MAX(T) AS MAX_T
                   FROM MY_TAB
                   WHERE FOO <> 'bar'
                   GROUP BY A, B, C
                   ORDER BY MAX(T))
SELECT *
  FROM (SELECT mt.*, ROWNUM AS RN
          FROM MY_TAB mt
          INNER JOIN cteData d
          ON d.A = mt.A AND
             d.B = mt.B AND
             d.C = mt.C AND
             d.MAX_T = mt.T) sq
  WHERE sq.RN BETWEEN 20 AND 30

Answer 4

The solution is mentioned in the comments, but just to summarize: Two things to be done here. 1) Add t to the where condition 2) Remove the order by in the subquery:

SELECT * 
FROM my_tab 
WHERE (a,b,c,t) IN
         (select a,b,c, max(t) as t from my_tab where foo<>'bar'
          group by a,b,c) 
  and rownum between 20 and 30;

Hope this helps.