Re: Need help with a query

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 15 Dec 2009 20:53:51 +0100
Message-ID: <4B27E94F.8080705_at_gmail.com>

On 15.12.2009 16:04, Charles Hooper wrote:
> On Dec 15, 6:00 am, Sandy80<svarshneym..._at_gmail.com> wrote:
>> Hi,
>>
>> I need help with a query which involves the 2 tables defined below.
>> What I need to do is choose the record with the max “Eff Date” from
>> “Table A” for a particular “Emp No.” and update the “Desc” from that
>> record in the field “Desc” of “Table B” for the same “Emp No.”. I am
>> able to choose the max “Eff Date” record for each employee from Table
>> A but somehow not able to updated the same “Desc” in “Table B”.
>>
>> Request you to please help the query. Any help would be appreciated.
>> Thanks!
>>
>> Table A
>> Emp No. Group Eff Date Desc
>> 1234 CI 01/01/1989 X
>> 1234 CI 01/02/2000 X
>> 1234 CI 01/02/2006 A
>> 2345 AF 01/01/1990 X
>> 2345 AF 01/02/2005 A
>>
>> Table B
>> Emp No. Group Desc
>> 1234 CI X
>> 2345 AF A
>> 3456 CI A
>
> When requesting assistance, please provide the necessary DDL and DML
> to create the test case. Also, post what you have tried so far.
>
> Watch the query and results closely as one possible solution is built
> (there are other methods):
> CREATE TABLE T1 (
> EMP_NO NUMBER,
> GROUPING VARCHAR2(5),
> EFF_DATE DATE,
> DESCR VARCHAR2(5));
>
> CREATE TABLE T2 (
> EMP_NO NUMBER,
> GROUPING VARCHAR2(5),
> DESCR VARCHAR2(5));
>
> INSERT INTO T1 VALUES (1234,'CI',TO_DATE('01/01/1989','MM/DD/
> YYYY'),'X');
> INSERT INTO T1 VALUES (1234,'CI',TO_DATE('01/02/2000','MM/DD/
> YYYY'),'X');
> INSERT INTO T1 VALUES (1234,'CI',TO_DATE('01/02/2006','MM/DD/
> YYYY'),'A');
> INSERT INTO T1 VALUES (2345,'AF',TO_DATE('01/01/1990','MM/DD/
> YYYY'),'X');
> INSERT INTO T1 VALUES (2345,'AF',TO_DATE('01/02/2005','MM/DD/
> YYYY'),'A');
>
> INSERT INTO T2 VALUES (1234,'CI','XNN');
> INSERT INTO T2 VALUES (2345,'AF','ANN');
> INSERT INTO T2 VALUES (3456,'CI','ANN');
>
> COMMIT;
>
> SELECT
> EMP_NO,
> GROUPING,
> DESCR
> FROM
> T2;
>
> EMP_NO GROUP DESCR
> ------ ----- -----
> 1234 CI XNN
> 2345 AF ANN
> 3456 CI ANN
>
>
> SELECT
> EMP_NO,
> GROUPING,
> EFF_DATE,
> DESCR
> FROM
> T1;
>
> EMP_NO GROUP EFF_DATE DESCR
> ------ ----- --------- -----
> 1234 CI 01-JAN-89 X
> 1234 CI 02-JAN-00 X
> 1234 CI 02-JAN-06 A
> 2345 AF 01-JAN-90 X
> 2345 AF 02-JAN-05 A
>
>
> SELECT
> EMP_NO,
> GROUPING,
> EFF_DATE,
> ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE
> DESC) RN,
> DESCR
> FROM
> T1;
>
> EMP_NO GROUP EFF_DATE RN DESCR
> ------ ----- --------- ---------- -----
> 1234 CI 02-JAN-06 1 A
> 1234 CI 02-JAN-00 2 X
> 1234 CI 01-JAN-89 3 X
> 2345 AF 02-JAN-05 1 A
> 2345 AF 01-JAN-90 2 X
>
>
> SELECT
> EMP_NO,
> GROUPING,
> EFF_DATE,
> DESCR
> FROM
> (SELECT
> EMP_NO,
> GROUPING,
> EFF_DATE,
> ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE
> DESC) RN,
> DESCR
> FROM
> T1)
> WHERE
> RN=1;
>
> EMP_NO GROUP EFF_DATE DESCR
> ------ ----- --------- -----
> 1234 CI 02-JAN-06 A
> 2345 AF 02-JAN-05 A
>
>
> UPDATE
> T2
> SET
> DESCR=(
> SELECT
> DESCR
> FROM
> (SELECT
> EMP_NO,
> GROUPING,
> ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY
> EFF_DATE DESC) RN,
> DESCR
> FROM
> T1) T1
> WHERE
> RN=1
> AND T1.EMP_NO=T2.EMP_NO
> AND T1.GROUPING=T2.GROUPING)
> WHERE
> (T2.EMP_NO,T2.GROUPING) IN (
> SELECT
> EMP_NO,
> GROUPING
> FROM
> (SELECT
> EMP_NO,
> GROUPING,
> ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY
> EFF_DATE DESC) RN,
> DESCR
> FROM
> T1)
> WHERE
> RN=1);
>
> 2 rows updated.
>
>
> SELECT
> EMP_NO,
> GROUPING,
> DESCR
> FROM
> T2;
>
> EMP_NO GROUP DESCR
> ------ ----- -----
> 1234 CI A
> 2345 AF A
> 3456 CI ANN
>
> Note that in the above, I assumed that the combination of EMP_NO and
> GROUPING had to be the same.
>
> Charles Hooper
> Co-author of "Expert Oracle Practices: Oracle Database Administration
> from the Oak Table"
> http://hoopercharles.wordpress.com/
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Alternatively, ( of course, if the version allows), update path of merge can be used:

SQL> merge into t2 t2

   2 using (
   3 select emp_no,grouping,

   4      max(descr) keep(dense_rank last order by eff_date) descr
   5      from t1 group by emp_no,grouping) t1

   6 on (t1.emp_no=t2.emp_no
   7 and t1.grouping=t2.grouping)
   8 when matched then update set t2.descr=t1.descr    9 ;

2 rows merged.

Execution Plan

---

Plan hash value: 3235844370

---

| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| 
Time     |
-------------------------------------------------------------------------------
|   0 | MERGE STATEMENT        |      |     2 |    16 |     8  (25)| 
00:00:01 |
|   1 |  MERGE                 | T2   |       |       |            | 
       |
|   2 |   VIEW                 |      |       |       |            | 
       |
|*  3 |    HASH JOIN           |      |     2 |   108 |     8  (25)| 
00:00:01 |
|   4 |     TABLE ACCESS FULL  | T2   |     3 |    99 |     3   (0)| 
00:00:01 |
|   5 |     VIEW               |      |     5 |   105 |     4  (25)| 
00:00:01 |
|   6 |      SORT GROUP BY     |      |     5 |   150 |     4  (25)| 
00:00:01 |
|   7 |       TABLE ACCESS FULL| T1   |     5 |   150 |     3   (0)| 

00:00:01 |

---

Predicate Information (identified by operation id):

---

    3 - access("T1"."EMP_NO"="T2"."EMP_NO" AND

               "T1"."GROUPING"="T2"."GROUPING") Best regards

Maxim Received on Tue Dec 15 2009 - 13:53:51 CST