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 |
|
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 #631595 is a reply to message #631592] |
Thu, 15 January 2015 14:57 |
|
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 #631598 is a reply to message #631594] |
Thu, 15 January 2015 16:10 |
|
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 #631600 is a reply to message #631599] |
Thu, 15 January 2015 18:23 |
|
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 |
|
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 #631603 is a reply to message #631602] |
Thu, 15 January 2015 21:08 |
Solomon Yakobson
Messages: 3267 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
marouene.lm wrote on Thu, 15 January 2015 21:03You 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.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Mar 18 21:36:31 CDT 2024
|