Home » SQL & PL/SQL » SQL & PL/SQL » issue with a select query using row_number (oracle pl/sql)
issue with a select query using row_number [message #631592] Thu, 15 January 2015 14:14 Go to next message
marouene.lm
Messages: 5
Registered: January 2015
Location: Tunisie
Junior Member
Hi,
the following query does not always retrieve an existing record for the same input parameters (emp_x_ind_in,emp_y_ind_in,extr_tag_in,extr_type_in,cmpy_in and usage_in)!

SELECT emp_num, acnt
INTO  l_emp_num,l_acnt
FROM
    (SELECT txtx.EMP_X_IND ,
        txtx.EMP_Y_IND ,
        txtx.EXTR_TAG          ,
        txtx.EXTR_TYPE         ,
        txtx.CMPY_NUM          ,
        txtx.USAGE             ,
        txtx.SORT_ORDER        ,
        txtx.x_role_code   ,
        extp.emp_num    ,
        extp.acnt              ,
        row_number() over ( partition BY txtx.EXTR_TAG
                        , txtx.EXTR_TYPE
                        , txtx.CMPY_NUM
                        , txtx.EMP_Y_IND
                        , txtx.EMP_X_IND
                        , txtx.USAGE order by txtx.sort_order ASC ) AS position
       FROM emp_usage txtx
       , employee extp
        WHERE txtx.extp_tag = extp.extp_tag
    )
WHERE position = 1
AND EMP_X_IND         = emp_x_ind_in
AND EMP_Y_IND         = emp_y_ind_in
AND EXTR_TAG          = extr_tag_in
AND EXTR_TYPE         = extr_type_in
AND cmpy              = cmpy_in
AND usage             = usage_in
;


please note that txtx.EXTR_TAG, txtx.EXTR_TYPE, txtx.CMPY_NUM, txtx.EMP_Y_IND, txtx.EMP_X_IND, txtx.USAGE and txtx.sort_order ensure a deterministic sort order!

any help would be appreciated.

Thanks.


[mod-edit: code tags added by bb]

[Updated on: Thu, 15 January 2015 14:47] by Moderator

Report message to a moderator

Re: issue with a select query using row_number [message #631594 is a reply to message #631592] Thu, 15 January 2015 14:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Since we don't have your tables or your data, what specific help do you expect from us since we can not begin to run, test, or debug posted code?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/


Re: issue with a select query using row_number [message #631595 is a reply to message #631592] Thu, 15 January 2015 14:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Your query is picking the first row, then making the comparisons with your variables. So, you need to move those comparisons from the outer query to the inner sub-query, as shown below, so that it first compares, then takes the first matching row.

SELECT emp_num, acnt
INTO  l_emp_num,l_acnt
FROM
    (SELECT txtx.EMP_X_IND ,
        txtx.EMP_Y_IND ,
        txtx.EXTR_TAG          ,
        txtx.EXTR_TYPE         ,
        txtx.CMPY_NUM          ,
        txtx.USAGE             ,
        txtx.SORT_ORDER        ,
        txtx.x_role_code   ,
        extp.emp_num    ,
        extp.acnt              ,
        row_number() over ( partition BY txtx.EXTR_TAG
                        , txtx.EXTR_TYPE
                        , txtx.CMPY_NUM
                        , txtx.EMP_Y_IND
                        , txtx.EMP_X_IND
                        , txtx.USAGE order by txtx.sort_order ASC ) AS position
       FROM emp_usage txtx
       , employee extp
        WHERE txtx.extp_tag = extp.extp_tag
        AND EMP_X_IND         = emp_x_ind_in
        AND EMP_Y_IND         = emp_y_ind_in
        AND EXTR_TAG          = extr_tag_in
        AND EXTR_TYPE         = extr_type_in
        AND cmpy              = cmpy_in
        AND usage             = usage_in
    )
WHERE position = 1
;


Re: issue with a select query using row_number [message #631596 is a reply to message #631595] Thu, 15 January 2015 15:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
Hi Barbara,

No need to partition by columns listed in WHERE clause equality conditions.

SY.
Re: issue with a select query using row_number [message #631598 is a reply to message #631594] Thu, 15 January 2015 16:10 Go to previous messageGo to next message
marouene.lm
Messages: 5
Registered: January 2015
Location: Tunisie
Junior Member
Thanks BlackSwan for sharing the 2 links.

some more details as requested:

desc employee;

/* generated by desc Thu Jan 15 20:43:04 2015 */
create table EMPLOYEE (
    EXTP_TAG                       NUMBER   (10)        NOT NULL,
    EXTR_TYPE                      VARCHAR2 (3 CHAR)    NOT NULL,
    EXTR_TAG                       NUMBER   (10)        NOT NULL,
    CMPY_NUM                       NUMBER   (5)         NOT NULL,
    EMP_X_IND                      VARCHAR2 (1 CHAR)    NOT NULL,
    EMP_Y_IND                      VARCHAR2 (1 CHAR)    NOT NULL,
    X_ROLE_CODE                    VARCHAR2 (3 CHAR)    NOT NULL,
    EMP_NUM                        NUMBER   (10)                ,
    EXTR_ADDR_1                    VARCHAR2 (35 CHAR)           ,
    EXTR_ADDR_2                    VARCHAR2 (35 CHAR)           ,
    ACNT                           VARCHAR2 (35 CHAR)           ,
    SUB_ACNT                       VARCHAR2 (35 CHAR)           ,
    ADR_INFO_1                     VARCHAR2 (35 CHAR)           ,
    ADR_INFO_2                     VARCHAR2 (35 CHAR)           ,
    USERNAME                       VARCHAR2 (16 CHAR)   NOT NULL,
    LAST_CHG_DATE                  DATE                 NOT NULL,
constraint EMPLOYEE_PK PRIMARY KEY ( EXTP_TAG )
) tablespace EBOND;
create UNIQUE index EMPLOYEE_2_IDX on EMPLOYEE
    ( EXTR_TAG, EXTR_TYPE, CMPY_NUM, X_ROLE_CODE, EMP_X_IND, EMP_Y_IND );
create UNIQUE index SM_PARTY_PK on EMPLOYEE
    ( EXTP_TAG );


desc emp_usage;

/* generated by desc Thu Jan 15 20:43:25 2015 */
create table EMP_USAGE (
    EXTP_TAG                       NUMBER   (10)        NOT NULL,
    USAGE                          VARCHAR2 (3 CHAR)    NOT NULL,
    EXTR_TYPE                      VARCHAR2 (3 CHAR)    NOT NULL,
    EXTR_TAG                       NUMBER   (10)        NOT NULL,
    CMPY_NUM                           NUMBER   (5)         NOT NULL,
    EMP_X_IND                      VARCHAR2 (1 CHAR)    NOT NULL,
    EMP_Y_IND                      VARCHAR2 (1 CHAR)    NOT NULL,
    X_ROLE_CODE                    VARCHAR2 (20 CHAR)   NOT NULL,
    SORT_ORDER                     NUMBER   (2)         NOT NULL,
constraint EMP_USAGE_PK PRIMARY KEY ( EXTR_TAG,EXTR_TYPE,CMPY_NUM,EMP_X_IND,EMP_Y_IND,USAGE,X_ROLE_CODE )
) tablespace EBOND;
create        index EMP_USAGE_1_IDX on EMP_USAGE
    ( X_ROLE_CODE, USAGE, SORT_ORDER );
create        index EMP_USAGE_2_IDX on EMP_USAGE
    ( EXTP_TAG );
create UNIQUE index EMP_USAGE_PK on EMP_USAGE
    ( EXTR_TAG, EXTR_TYPE, CMPY_NUM, EMP_X_IND, EMP_Y_IND, USAGE, X_ROLE_CODE );

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Re: issue with a select query using row_number [message #631599 is a reply to message #631595] Thu, 15 January 2015 16:18 Go to previous messageGo to next message
marouene.lm
Messages: 5
Registered: January 2015
Location: Tunisie
Junior Member
Hi Barbara,

Thanks for your suggestion. could you please explain why changing the query, as you mentioned, will resolve my issue?

Regards,

[Updated on: Thu, 15 January 2015 16:38]

Report message to a moderator

Re: issue with a select query using row_number [message #631600 is a reply to message #631599] Thu, 15 January 2015 18:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
As Solomon noted, you don't need the partition, so the following should suffice.

SELECT emp_num, acnt
INTO   l_emp_num, l_acnt
FROM   (SELECT extp.emp_num,
               extp.acnt,
               row_number() over (order by txtx.sort_order ASC) AS position
        FROM   emp_usage txtx, 
               employee extp
        WHERE  txtx.extp_tag = extp.extp_tag
        AND    txtx.EMP_X_IND     = emp_x_ind_in
        AND    txtx.EMP_Y_IND     = emp_y_ind_in
        AND    txtx.EXTR_TAG      = extr_tag_in
        AND    txtx.EXTR_TYPE     = extr_type_in
        AND    txtx.cmpy_num      = cmpy_in
        AND    txtx.usage         = usage_in)
WHERE  position = 1;

[Updated on: Thu, 15 January 2015 18:52]

Report message to a moderator

Re: issue with a select query using row_number [message #631601 is a reply to message #631600] Thu, 15 January 2015 19:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Perhaps the following simplified example will make it clearer. I have provided separate results for the inner sub-queries. Oracle processes the inner sub-query first, then the outer query. Note the differences in row_number when a condition is applied or not applied within the inner sub-query.

-- test data:
SCOTT@orcl> SELECT emp_num, acnt, extp_tag FROM employee
  2  /

   EMP_NUM ACNT                                  EXTP_TAG
---------- ----------------------------------- ----------
         1 Acnt1                                       10
         2 Acnt2                                       20
         3 Acnt3                                       30

3 rows selected.

SCOTT@orcl> SELECT extp_tag, emp_x_ind, sort_order FROM emp_usage
  2  /

  EXTP_TAG E SORT_ORDER
---------- - ----------
        10 A          1
        20 B          2
        30 C          3

3 rows selected.


-- wrong:
SCOTT@orcl> -- inner sub-query:
SCOTT@orcl> SELECT extp.emp_num,
  2  		    extp.acnt,
  3  		    txtx.emp_x_ind,
  4  		    row_number() over (order by txtx.sort_order ASC ) AS position
  5  	     FROM   emp_usage txtx,
  6  		    employee extp
  7  	     WHERE  txtx.extp_tag = extp.extp_tag
  8  /

   EMP_NUM ACNT                                E   POSITION
---------- ----------------------------------- - ----------
         1 Acnt1                               A          1
         2 Acnt2                               B          2
         3 Acnt3                               C          3

3 rows selected.

SCOTT@orcl> -- full query:
SCOTT@orcl> SELECT emp_num, acnt
  2  FROM   (SELECT extp.emp_num,
  3  		    extp.acnt,
  4  		    txtx.emp_x_ind,
  5  		    row_number() over (order by txtx.sort_order ASC ) AS position
  6  	     FROM   emp_usage txtx,
  7  		    employee extp
  8  	     WHERE  txtx.extp_tag = extp.extp_tag)
  9  WHERE  position = 1
 10  AND    EMP_X_IND = 'B'
 11  /

no rows selected


-- right:
SCOTT@orcl> -- inner sub-query:
SCOTT@orcl> SELECT extp.emp_num,
  2  		    extp.acnt,
  3  		    txtx.emp_x_ind,
  4  		    row_number() over (order by txtx.sort_order ASC ) AS position
  5  	     FROM   emp_usage txtx,
  6  		    employee extp
  7  	     WHERE  txtx.extp_tag = extp.extp_tag
  8  	     AND    txtx.EMP_X_IND     = 'B'
  9  /

   EMP_NUM ACNT                                E   POSITION
---------- ----------------------------------- - ----------
         2 Acnt2                               B          1

1 row selected.

SCOTT@orcl> -- full query:
SCOTT@orcl> SELECT emp_num, acnt
  2  FROM   (SELECT extp.emp_num,
  3  		    extp.acnt,
  4  		    txtx.emp_x_ind,
  5  		    row_number() over (order by txtx.sort_order ASC ) AS position
  6  	     FROM   emp_usage txtx,
  7  		    employee extp
  8  	     WHERE  txtx.extp_tag = extp.extp_tag
  9  	     AND    txtx.EMP_X_IND     = 'B')
 10  WHERE  position = 1
 11  /

   EMP_NUM ACNT
---------- -----------------------------------
         2 Acnt2

1 row selected.

Re: issue with a select query using row_number [message #631602 is a reply to message #631601] Thu, 15 January 2015 20:03 Go to previous messageGo to next message
marouene.lm
Messages: 5
Registered: January 2015
Location: Tunisie
Junior Member
Thanks Barbara for the explaination... but still think that my query and yours will retrieve always the same record.

You have to change the "wrong" query in your example like the following by adding "partition by" so that it will be similar to my query:
SCOTT@orcl> -- full query:
SCOTT@orcl> SELECT emp_num, acnt
  2  FROM   (SELECT extp.emp_num,
  3  		    extp.acnt,
  4  		    txtx.emp_x_ind,
  5  		    row_number() over (partition by txtx.EMP_X_IND order by txtx.sort_order ASC ) AS position
  6  	     FROM   emp_usage txtx,
  7  		    employee extp
  8  	     WHERE  txtx.extp_tag = extp.extp_tag)
  9  WHERE  position = 1
 10  AND    EMP_X_IND = 'B'


Regards.
Re: issue with a select query using row_number [message #631603 is a reply to message #631602] Thu, 15 January 2015 21:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
marouene.lm wrote on Thu, 15 January 2015 21:03
You have to change the "wrong" query in your example like the following by adding "partition by" so that it will be similar to my query:



Again, there is no need to partition. Your inline view joins all rows in emp_usage table with matching rows in employee table. Then it partitions results by EMP_X_IND and numbers rows in each partition. Then main query select first row in partition where EMP_X_IND = 'B'. In other words, your query waisted all the resources needed to join, partition and number rows where EMP_X_IND != 'B' or is NULL. All you need is join emp_usage table rows where EMP_X_IND = 'B' with matching rows in employee table. That is equivalent one EMP_X_IND = 'B' partition of your inline view. Then number rows and select first one.

SY.
Re: issue with a select query using row_number [message #631605 is a reply to message #631602] Thu, 15 January 2015 21:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It looks like the original query should return the same results as the final query that I provided, but much less efficiently. As to why the original query did not always return an existing record, it is hard to guess without some data and variables values that reproduce the problem.

[Updated on: Thu, 15 January 2015 22:00]

Report message to a moderator

Re: issue with a select query using row_number [message #631792 is a reply to message #631605] Mon, 19 January 2015 16:36 Go to previous messageGo to next message
marouene.lm
Messages: 5
Registered: January 2015
Location: Tunisie
Junior Member
Thanks Barbara and Solomon for your explanations.

Please note that the 2 queries retrun the same explain plan however I was expecting that they should be different as the query provided by Barabara will be more efficient! Could you please explain?

Regards.
Re: issue with a select query using row_number [message #631795 is a reply to message #631792] Mon, 19 January 2015 21:28 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Perhaps the Oracle optimizer is smarter than we think and rewrites the less efficient query to use the same plan as the more efficient one.
Previous Topic: Logic to create new column in Oracle database - Multiple conditions making it more challenging
Next Topic: How to get right side value after comma
Goto Forum:
  


Current Time: Mon Mar 18 21:36:31 CDT 2024