Need a Help in Select statement & Update query

Database

Need a Help in Select statement & Update query


Hi Gurus,

Can anyone share the Log for this I have tried but there is no luck.

we have a Duplicate records and we are nt Deleting the Duplicate records instead we are Updating the records but i am not even close to write a query for select statement to get the required output.

Main, to update the old records from the table based on the IN_date and out_date

the issue is we have duplicate records on 10th Dec 2011 and we are updating the records based on the IN_DATE with the Previous day of OUT_DATE

under each PARTTYPE ==> each MFGTYPE ==> having Multiple currencies

Here is the Metadata

Create MULTISET Table T_PLANT12
(
plant_no INTEGER NOT NULL
,part1 CHAR(9)
,partType CHAR(9)
,MfgType CHAR(9)
,currency CHAR(9)
,cost INTEGER
,create_no INTEGER NOT NULL
,IN_date DATE FORMAT 'mm/dd/yy'
,out_date DATE FORMAT 'mm/dd/yy'
);

/********************************************************************************************************************************/

insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'CA', 1 ,348359,TO_DATE('12-10-2011', 'MM-DD-YYYY'), TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'CA', 1 ,412575,TO_DATE('12-10-2011', 'MM-DD-YYYY'), TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'CA', 1 ,413061,TO_DATE('12-14-2011', 'MM-DD-YYYY'),TO_DATE('12-15-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'CA', 1 ,413524,TO_DATE('12-15-2011', 'MM-DD-YYYY'),TO_DATE('12-22-2011', 'MM-DD-YYYY'));

insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'EU', 2 ,348359,TO_DATE('12-10-2011', 'MM-DD-YYYY'),TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'EU', 2 ,412575,TO_DATE('12-10-2011', 'MM-DD-YYYY'),TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'EU', 2 ,413061,TO_DATE('12-14-2011', 'MM-DD-YYYY'),TO_DATE('12-15-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'EU', 2 ,413524,TO_DATE('12-15-2011', 'MM-DD-YYYY'),TO_DATE('12-22-2011', 'MM-DD-YYYY'));

insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'US', 4 ,348359,TO_DATE('12-10-2011', 'MM-DD-YYYY'),TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'US', 4 ,412575,TO_DATE('12-10-2011', 'MM-DD-YYYY'),TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'US', 6 ,413061,TO_DATE('12-14-2011', 'MM-DD-YYYY'),TO_DATE('12-15-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'US', 6 ,413524,TO_DATE('12-15-2011', 'MM-DD-YYYY'), TO_DATE('12-22-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'US', 20 ,437576,TO_DATE('12-22-2011', 'MM-DD-YYYY'), TO_DATE('12-30-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'US', 20 ,511842,TO_DATE('12-30-2011', 'MM-DD-YYYY'), TO_DATE('01-04-2012', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'US', 20 ,551942,TO_DATE('01-04-2012', 'MM-DD-YYYY'), TO_DATE('01-21-2012', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'X', 'US', 20 ,712134,TO_DATE('01-21-2012', 'MM-DD-YYYY'), TO_DATE('12-31-9999', 'MM-DD-YYYY'));

insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'T', 'US', 15 ,437576,TO_DATE('12-22-2011', 'MM-DD-YYYY'), TO_DATE('12-30-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'T', 'US', 15 ,511842,TO_DATE('12-30-2011', 'MM-DD-YYYY'), TO_DATE('01-04-2012', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'T', 'US', 15 ,551942,TO_DATE('01-04-2012', 'MM-DD-YYYY'), TO_DATE('01-21-2012', 'MM-DD-YYYY'));
insert into T_PLANT12 values
(1,'Japan','A11','B22','RS', 'T', 'US', 15 ,712134,TO_DATE('01-21-2012', 'MM-DD-YYYY'), TO_DATE('12-31-9999', 'MM-DD-YYYY'));

/*********************************************************************/

insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'L', 'EU', 1, 347196,TO_DATE('12-10-2011', 'MM-DD-YYYY'), TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'L', 'EU', 1, 347868,TO_DATE('12-10-2011', 'MM-DD-YYYY'), TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'L', 'EU', 1, 348359,TO_DATE('12-10-2011', 'MM-DD-YYYY'), TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'L', 'EU', 1, 412575,TO_DATE('12-10-2011', 'MM-DD-YYYY'), TO_DATE('12-10-2011', 'MM-DD-YYYY'));

insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'L', 'US', 2, 488093, TO_DATE('12-28-2011', 'MM-DD-YYYY'), TO_DATE('01-11-2012', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'L', 'US', 2, 613097, TO_DATE('01-11-2012', 'MM-DD-YYYY'), TO_DATE('01-21-2012', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'L', 'US', 2, 712134,TO_DATE('01-21-2012', 'MM-DD-YYYY'), TO_DATE('12-31-9999', 'MM-DD-YYYY'));

insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'X', 'EU', 1, 347196,TO_DATE('12-10-2011', 'MM-DD-YYYY'), TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'X', 'EU', 1, 347868,TO_DATE('12-10-2011', 'MM-DD-YYYY'), TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'X', 'EU', 1, 348359,TO_DATE('12-10-2011', 'MM-DD-YYYY'), TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'X', 'EU', 1, 412575,TO_DATE('12-10-2011', 'MM-DD-YYYY'), TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'X', 'US', 2, 488093,TO_DATE('12-28-2011', 'MM-DD-YYYY'), TO_DATE('01-11-2012', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'X', 'US', 2, 613097,TO_DATE('01-11-2012', 'MM-DD-YYYY'), TO_DATE('01-21-2012', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','RS', 'X', 'US', 2, 712134,TO_DATE('01-21-2012', 'MM-DD-YYYY'), TO_DATE('12-31-9999', 'MM-DD-YYYY'));

insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','TP', 'T', 'US', 2, 488093,TO_DATE('12-28-2011', 'MM-DD-YYYY'), TO_DATE('01-11-2012', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','TP', 'T', 'US', 2, 613097,TO_DATE('01-11-2012', 'MM-DD-YYYY'), TO_DATE('01-21-2012', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 2,'Malaysia','A10','B20','TP', 'T', 'US', 2, 712134,TO_DATE('01-21-2012', 'MM-DD-YYYY'), TO_DATE('12-31-9999', 'MM-DD-YYYY'));

/*********************************************************************/

insert into T_PLANT12 values
( 3,'Thailand','A09','B20','RS', 'X', 'EU', 1, 347186,TO_DATE('12-07-2011', 'MM-DD-YYYY'), TO_DATE('12-08-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 3,'Thailand','A09','B20','RS', 'X', 'EU', 1, 347818,TO_DATE('12-08-2011', 'MM-DD-YYYY'), TO_DATE('12-09-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 3,'Thailand','A09','B20','RS', 'X', 'EU', 1, 348359,TO_DATE('12-10-2011', 'MM-DD-YYYY'), TO_DATE('12-10-2011', 'MM-DD-YYYY'));
insert into T_PLANT12 values
( 3,'Thailand','A09','B20','RS', 'X', 'EU', 1, 412575,TO_DATE('12-10-2011', 'MM-DD-YYYY'), TO_DATE('12-10-2011', 'MM-DD-YYYY'));

Here is the Query and Output what i have tried, i just got the result for scenario 1st but i need for the scenario 2nd and scenario 3rd

 i need to Table data as below result set . 

SELECT T_PLANT12 .* 
,COALESCE(
MIN(IN_date)
OVER(PARTITION BY plant_no, part1, parttype, mfgtype, currency
ORDER BY parttype,mfgtype,create_no
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING )
--,CAST('9999-12-31'AS DATE) ) AS Prev_OUT_DATE
,out_date ) AS Prev_out_date
FROM T_PLANT12
ORDER BY plant_no, part1,parttype, mfgtype, currency,create_no

here is the required output

plant_no part1 partType MfgType currency cost create_no IN_date out_date Required OUTPUT

 plant_no part1 partType MfgType currency cost create_no IN_date out_date Prev_out_date
1 1 A11 RS T US 15 437,576 12/22/2011 12/30/2011 12/30/2011
2 1 A11 RS T US 15 511,842 12/30/2011 1/4/2012 1/4/2012
3 1 A11 RS T US 15 551,942 1/4/2012 1/21/2012 1/21/2012
4 1 A11 RS T US 15 712,134 1/21/2012 12/31/9999 12/31/9999
5 1 A11 RS X CA 1 348,359 12/10/2011 12/10/2011 12/10/2011
6 1 A11 RS X CA 1 412,575 12/10/2011 12/10/2011 12/14/2011
7 1 A11 RS X CA 1 413,061 12/14/2011 12/15/2011 12/15/2011
8 1 A11 RS X CA 1 413,524 12/15/2011 12/22/2011 12/22/2011
9 1 A11 RS X EU 2 348,359 12/10/2011 12/10/2011 12/10/2011
10 1 A11 RS X EU 2 412,575 12/10/2011 12/10/2011 12/14/2011
11 1 A11 RS X EU 2 413,061 12/14/2011 12/15/2011 12/15/2011
12 1 A11 RS X EU 2 413,524 12/15/2011 12/22/2011 12/22/2011
13 1 A11 RS X US 4 348,359 12/10/2011 12/10/2011 12/10/2011
14 1 A11 RS X US 4 412,575 12/10/2011 12/10/2011 12/14/2011
15 1 A11 RS X US 6 413,061 12/14/2011 12/15/2011 12/15/2011
16 1 A11 RS X US 6 413,524 12/15/2011 12/22/2011 12/22/2011
17 1 A11 RS X US 20 437,576 12/22/2011 12/30/2011 12/30/2011
18 1 A11 RS X US 20 511,842 12/30/2011 1/4/2012 1/4/2012
19 1 A11 RS X US 20 551,942 1/4/2012 1/21/2012 1/21/2012
20 1 A11 RS X US 20 712,134 1/21/2012 12/31/9999 12/31/9999
21 1 A11 TP T US 15 437,576 12/22/2011 12/30/2011 12/30/2011
22 1 A11 TP T US 15 511,842 12/30/2011 1/4/2012 1/4/2012
23 1 A11 TP T US 15 551,942 1/4/2012 1/21/2012 1/21/2012
24 1 A11 TP T US 15 712,134 1/21/2012 12/31/9999 12/31/9999
25 2 A10 RS L EU 1 347,196 12/10/2011 12/10/2011 12/10/2011
26 2 A10 RS L EU 1 347,868 12/10/2011 12/10/2011 12/10/2011
27 2 A10 RS L EU 1 348,359 12/10/2011 12/10/2011 12/10/2011
28 2 A10 RS L EU 1 412,575 12/10/2011 12/10/2011 12/10/2011
29 2 A10 RS L US 2 488,093 12/28/2011 1/11/2012 1/11/2012
30 2 A10 RS L US 2 613,097 1/11/2012 1/21/2012 1/21/2012
31 2 A10 RS L US 2 712,134 1/21/2012 12/31/9999 12/31/9999
32 2 A10 RS X EU 1 347,196 12/10/2011 12/10/2011 12/10/2011
33 2 A10 RS X EU 1 347,868 12/10/2011 12/10/2011 12/10/2011
34 2 A10 RS X EU 1 348,359 12/10/2011 12/10/2011 12/10/2011
35 2 A10 RS X EU 1 412,575 12/10/2011 12/10/2011 12/10/2011
36 2 A10 RS X US 2 488,093 12/28/2011 1/11/2012 1/11/2012
37 2 A10 RS X US 2 613,097 1/11/2012 1/21/2012 1/21/2012
38 2 A10 RS X US 2 712,134 1/21/2012 12/31/9999 12/31/9999
39 2 A10 TP T US 2 488,093 12/28/2011 1/11/2012 1/11/2012
40 2 A10 TP T US 2 613,097 1/11/2012 1/21/2012 1/21/2012
41 2 A10 TP T US 2 712,134 1/21/2012 12/31/9999 12/31/9999
42 3 A09 RS X EU 1 347,186 12/7/2011 12/8/2011 12/8/2011
43 3 A09 RS X EU 1 347,818 12/8/2011 12/9/2011 12/10/2011
44 3 A09 RS X EU 1 348,359 12/10/2011 12/10/2011 12/10/2011
45 3 A09 RS X EU 1 412,575 12/10/2011 12/10/2011 12/10/2011
1 REPLY

Re: Need a Help in Select statement & Update query

Here is the correct Metadata

Create MULTISET Table T_PLANT12
(
plant_no INTEGER NOT NULL
,plant_Name CHAR(9)
,part1 CHAR(9)
,part2 CHAR(9)
,partType CHAR(9)
,MfgType CHAR(9)
,currency CHAR(9)
,cost INTEGER
,create_no INTEGER NOT NULL
,IN_date DATE FORMAT 'mm/dd/yy'
,out_date DATE FORMAT 'mm/dd/yy'
);
 plant_no part1 partType MfgType currency cost create_no IN_date out_date Required OUTPUT
scenario - 1
1 1 A11 RS T US 15 437,576 12/22/2011 12/30/2011 12/30/2011
2 1 A11 RS T US 15 511,842 12/30/2011 1/4/2012 1/4/2012
3 1 A11 RS T US 15 551,942 1/4/2012 1/21/2012 1/21/2012
4 1 A11 RS T US 15 712,134 1/21/2012 12/31/9999 12/31/9999
5 1 A11 RS X CA 1 348,359 12/10/2011 12/10/2011 12/10/2011
6 1 A11 RS X CA 1 412,575 12/10/2011 12/10/2011 12/14/2011
7 1 A11 RS X CA 1 413,061 12/14/2011 12/15/2011 12/15/2011
8 1 A11 RS X CA 1 413,524 12/15/2011 12/22/2011 12/22/2011
9 1 A11 RS X EU 2 348,359 12/10/2011 12/10/2011 12/10/2011
10 1 A11 RS X EU 2 412,575 12/10/2011 12/10/2011 12/14/2011
11 1 A11 RS X EU 2 413,061 12/14/2011 12/15/2011 12/15/2011
12 1 A11 RS X EU 2 413,524 12/15/2011 12/22/2011 12/22/2011
13 1 A11 RS X US 4 348,359 12/10/2011 12/10/2011 12/10/2011
14 1 A11 RS X US 4 412,575 12/10/2011 12/10/2011 12/14/2011
15 1 A11 RS X US 6 413,061 12/14/2011 12/15/2011 12/15/2011
16 1 A11 RS X US 6 413,524 12/15/2011 12/22/2011 12/22/2011
17 1 A11 RS X US 20 437,576 12/22/2011 12/30/2011 12/30/2011
18 1 A11 RS X US 20 511,842 12/30/2011 1/4/2012 1/4/2012
19 1 A11 RS X US 20 551,942 1/4/2012 1/21/2012 1/21/2012
20 1 A11 RS X US 20 712,134 1/21/2012 12/31/9999 12/31/9999
21 1 A11 TP T US 15 437,576 12/22/2011 12/30/2011 12/30/2011
22 1 A11 TP T US 15 511,842 12/30/2011 1/4/2012 1/4/2012
23 1 A11 TP T US 15 551,942 1/4/2012 1/21/2012 1/21/2012
24 1 A11 TP T US 15 712,134 1/21/2012 12/31/9999 12/31/9999
scenario - 2
25 2 A10 RS L EU 1 347,196 12/10/2011 12/10/2011 12/10/2011
26 2 A10 RS L EU 1 347,868 12/10/2011 12/10/2011 12/10/2011
27 2 A10 RS L EU 1 348,359 12/10/2011 12/10/2011 12/10/2011
28 2 A10 RS L EU 1 412,575 12/10/2011 12/10/2011 12/28/2011
29 2 A10 RS L US 2 488,093 12/28/2011 1/11/2012 1/11/2012
30 2 A10 RS L US 2 613,097 1/11/2012 1/21/2012 1/21/2012
31 2 A10 RS L US 2 712,134 1/21/2012 12/31/9999 12/31/9999
32 2 A10 RS X EU 1 347,196 12/10/2011 12/10/2011 12/10/2011
33 2 A10 RS X EU 1 347,868 12/10/2011 12/10/2011 12/10/2011
34 2 A10 RS X EU 1 348,359 12/10/2011 12/10/2011 12/10/2011
35 2 A10 RS X EU 1 412,575 12/10/2011 12/10/2011 12/28/2011
36 2 A10 RS X US 2 488,093 12/28/2011 1/11/2012 1/11/2012
37 2 A10 RS X US 2 613,097 1/11/2012 1/21/2012 1/21/2012
38 2 A10 RS X US 2 712,134 1/21/2012 12/31/9999 12/31/9999
39 2 A10 TP T US 2 488,093 12/28/2011 1/11/2012 1/11/2012
40 2 A10 TP T US 2 613,097 1/11/2012 1/21/2012 1/21/2012
41 2 A10 TP T US 2 712,134 1/21/2012 12/31/9999 12/31/9999
scenario - 3
42 3 A09 RS X EU 1 347,186 12/7/2011 12/8/2011 12/8/2011
43 3 A09 RS X EU 1 347,818 12/8/2011 12/9/2011 12/10/2011
44 3 A09 RS X EU 1 348,359 12/10/2011 12/10/2011 12/10/2011
45 3 A09 RS X EU 1 412,575 12/10/2011 12/10/2011 12/31/9999