Details

    • Type: Explanation
    • Status: Published
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
    • Symptoms:
      Hide

      How to use and what is the explanation for error
      "ROWNUM cannot be used in combination with this statement." ?

      Show
      How to use and what is the explanation for error "ROWNUM cannot be used in combination with this statement." ?
    • Solution:
      Hide

      EXASolution's ROWNUM is not compatible with Oracle's ROWNUM!

      While ROWNUM in Oracle can be used to limit output data of almost arbitrary statements, EXASolution implements ROWNUM while sticking to the overall SQL semantics: Anything you put into the WHERE clause of a statement filters input data. To avoid confusion and seemingly wrong results, we only allow ROWNUM in situations where the result is in line with Oracle's semantic.

      Example to clarify:

      Assumed there's a table "customer" containing a large number of "Schmitts".

      select * from customer where c_name like 'Schmitt%';
      -- works, but too many rows
      
      select * from customer where rownum < 11;
      -- works, only ten rows, but no "Schmitt's"
      
      select * 
           from customer 
        where c_name like 'Schmitt%' 
            and rownum < 11;
      -- Error, ROWNUM can't be combined with other conditions in where clause
      

      In the last statement, Oracle would first filter for all the Schmitts and only output the first 10 matches. Using strict SQL semantics, the filters on c_name and ROWNUM would be independent, meaning that only Schmitts appearing in the first 10 rows of the table get returned.
      As this is probably not what you expect (coming from Oracle), we prevent this statement: ROWNUM has to be the only one condition in the where clause

      Workaround / Solution

      -- use a subselect
      select *
          from (select *
                  from customer
                where c_name like 'Schmitt%')
        where rownum < 11;
      -- works, ten rows with "Schmitt's"
      
      -- use LIMIT instead of ROWNUM
      select *
          from customer
          where c_name like 'Schmitt%'
          LIMIT 10;
      

      Additionally, there are some statements which generally don't allow the
      usage of ROWNUM

      select c_name with invalid primary key (c_custkey) 
           from customer  where rownum < 11;
      -- Error, ROWNUM cannot be used in combination with this statement
      
      Show
      EXASolution's ROWNUM is not compatible with Oracle's ROWNUM! While ROWNUM in Oracle can be used to limit output data of almost arbitrary statements, EXASolution implements ROWNUM while sticking to the overall SQL semantics: Anything you put into the WHERE clause of a statement filters input data . To avoid confusion and seemingly wrong results, we only allow ROWNUM in situations where the result is in line with Oracle's semantic. Example to clarify: Assumed there's a table "customer" containing a large number of "Schmitts". select * from customer where c_name like 'Schmitt%' ; -- works, but too many rows select * from customer where rownum < 11; -- works, only ten rows , but no "Schmitt's" select * from customer where c_name like 'Schmitt%' and rownum < 11; -- Error, ROWNUM can't be combined with other conditions in where clause In the last statement, Oracle would first filter for all the Schmitts and only output the first 10 matches. Using strict SQL semantics, the filters on c_name and ROWNUM would be independent, meaning that only Schmitts appearing in the first 10 rows of the table get returned. As this is probably not what you expect (coming from Oracle), we prevent this statement: ROWNUM has to be the only one condition in the where clause Workaround / Solution -- use a subselect select * from ( select * from customer where c_name like 'Schmitt%' ) where rownum < 11; -- works, ten rows with "Schmitt's" -- use LIMIT instead of ROWNUM select * from customer where c_name like 'Schmitt%' LIMIT 10; Additionally, there are some statements which generally don't allow the usage of ROWNUM select c_name with invalid primary key (c_custkey) from customer where rownum < 11; -- Error, ROWNUM cannot be used in combination with this statement
    • Category 1:
      SQL

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: