Re: Need help with a query
Date: Tue, 15 Dec 2009 07:04:40 -0800 (PST)
Message-ID: <28bca873-508a-45bd-9773-91bddd627909_at_m3g2000yqf.googlegroups.com>
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.
Received on Tue Dec 15 2009 - 09:04:40 CST