5 Replies Latest reply: May 14, 2013 5:02 AM by BluShadow RSS

    Alternative for ROW_NUMBER () OVER.

    Nagaraja Akkivalli
      Hi All,

      Is there a way to implement the below logic without using ROW_NUMBER () OVER feature that Oracle provides ?
      SELECT
      cost_object INTO out_cost_object
      FROM
      (
      SELECT equip_no,
      cost_object,
      ROW_NUMBER () OVER (PARTITION BY equip_no, trunc(in_date)
      ORDER BY start_date) row_number
      FROM escost_object_assign
      WHERE equip_no = in_equip
      AND in_date between start_date and nvl(end_date,sysdate)
      
      )
      WHERE
      a.     row_number = 1 ;
      I want to implement this logic in one of the tool which does not support ROW_NUMBER OVER feature. Please let em know if you need any info.

      Thanks and Regards
      Nagaraja Akkivalli.
        • 1. Re: Alternative for ROW_NUMBER () OVER.
          ranit B
          I want to implement this logic in one of the tool which does not support ROW_NUMBER OVER feature. Please let em know if you need any info.
          Strange to hear that a 'Tool' not supporting some Oracle provided feature.
          Btw, by Tool did you mean a client like Toad or SQL Developer?

          Actually a feature is Oracle database-version dependent and not an IDE (client).
          • 2. Re: Alternative for ROW_NUMBER () OVER.
            Nagaraja Akkivalli
            Hi Ranit,

            Thanks for your response.

            Its not SQL Developer/Toad. Please let me know if you know alternative logic for this...


            Thanks
            Nagaraja.
            • 3. Re: Alternative for ROW_NUMBER () OVER.
              BluShadow
              Nagaraja Akkivalli wrote:
              Hi Ranit,

              Thanks for your response.

              Its not SQL Developer/Toad. Please let me know if you know alternative logic for this...


              Thanks
              Nagaraja.
              You'd be better to change your tool, rather than perfectly acceptable code.
              Ranit B wrote:
              Actually a feature is Oracle database-version dependent and not an IDE (client).
              Actually the client IDE does make a difference. The client needs to support the version of the database. Even old versions of SQL*Plus can complain about certain new syntax in SQL statements from newer database versions.
              • 4. Re: Alternative for ROW_NUMBER () OVER.
                ranit B
                Actually the client IDE does make a difference. The client needs to support the version of the database. Even old versions of SQL*Plus can complain about certain new syntax in SQL statements from newer database versions.
                Thanks for the information, Blu. I didn't know that.
                • 5. Re: Alternative for ROW_NUMBER () OVER.
                  AlbertoFaenza
                  Hi,

                  can you post the structure of escost_object_assign?

                  Also from your code it seem you are just returning one row (INTO out_cost_object).
                  But your analytic function seems to be handling more than one rows as you have partition by equip_no, trunc(in_date)

                  I suppose you can do something like this.
                  SELECT cost_object
                    INTO out_cost_object
                    FROM (SELECT cost_object
                            FROM escost_object_assign
                           WHERE equip_no = in_equip
                             AND in_date BETWEEN start_date AND NVL (end_date, SYSDATE)
                           ORDER BY start_date
                         )
                   WHERE ROWNUM <= 1;
                  If not, please post some sample data (CREATE TABLE and INSERT statement) and explain exactly the case.

                  Beside this I have to say that I totally agree with Blu:
                  You'd be better to change your tool, rather than perfectly acceptable code.
                  A tool should support entirely the database.

                  Regards.
                  Al

                  Edited by: Alberto Faenza on May 14, 2013 12:07 PM