| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-27-11, 16:00
|
|
Registered User
|
|
Join Date: Jul 2008
Posts: 80
|
|
|
Update query for cdc?
|
|
Code:
drop table temppp;
create table temppp(uniqueid integer,employeeid varchar(15),skillid varchar(10),sname varchar(90),prof varchar2(10),acode varchar(1),
adesc varchar(30),start_date date,end_date date,rind varchar(1));
insert into temppp values(3,10,'1','Unix','Basic','1','ABC',to_date(SYSDATE-1,'YYYY-MM-DD'),
to_date(SYSDATE-1,'YYYY-MM-DD'),'N');
insert into temppp values(4,10,'1','Unix Shell','Basic','2','XYZ',to_date(SYSDATE,'YYYY-MM-DD'),
to_date(NULL,'YYYY-MM-DD'),'Y');
insert into temppp values(5,10,'2','Pro*C','Basic','1','ABC',to_date(SYSDATE-1,'YYYY-MM-DD'),
to_date(SYSDATE-1,'YYYY-MM-DD'),'N');
insert into temppp values(5,10,'2','Pro*C','Expert','2','XYZ',to_date(SYSDATE,'YYYY-MM-DD'),
to_date(NULL,'YYYY-MM-DD'),'Y');
insert into temppp values(8,10,'5','Perl','Expert','1','XYZ',to_date(SYSDATE,'YYYY-MM-DD'),
to_date(NULL,'YYYY-MM-DD'),'Y');
insert into temppp values(1,10,'3','Java','Basic','1','ABC',to_date(SYSDATE-1,'YYYY-MM-DD'),
to_date(SYSDATE-1,'YYYY-MM-DD'),'N');
insert into temppp values(2,10,'3','Sun Java','Basic','2','XYZ',to_date(SYSDATE,'YYYY-MM-DD'),
to_date(NULL,'YYYY-MM-DD'),'Y');
insert into temppp values(3,11,'3','Java','Basic','1','ABC',to_date(SYSDATE-1,'YYYY-MM-DD'),
to_date(SYSDATE-1,'YYYY-MM-DD'),'N');
insert into temppp values(4,11,'3','Sun Java','Basic','2','XYZ',to_date(SYSDATE,'YYYY-MM-DD'),
to_date(NULL,'YYYY-MM-DD'),'Y');
insert into temppp values(1,11,'1','Unix','Basic','1','ABC',to_date(SYSDATE-1,'YYYY-MM-DD'),
to_date(SYSDATE-1,'YYYY-MM-DD'),'N');
insert into temppp values(2,11,'1','A Unix','Basic','2','XYZ',to_date(SYSDATE,'YYYY-MM-DD'),
to_date(NULL,'YYYY-MM-DD'),'Y');
Update temppp g
set g.acode='6',
g.adesc='M XYZ'
where
(g.employeeid,g.uniqueid) in
(Select f.employeeid,f.uniqueid
from
(select e.employeeid,e.uniqueid,
rank() over(partition by e.employeeid,e.skillid order by e.uniqueid) as M_change,
rank() over(partition by e.employeeid,e.skillid order by e.sname,e.prof) as B_change
from
(select a.*
from temppp a where
a.end_date=to_date(sysdate-1,'yyyy-mm-dd')
and a.rind='N'
union all
select c.*
from temppp c Where
c.start_date=to_date(sysdate,'yyyy-mm-dd')
and c.acode='2'
and c.rind='Y') e
) f where f.M_change=2
and f.B_change=1 ) and g.acode='2' and g.rind='Y'
1. When Skill Name and Unique ID changes then no need to update
2. When Skill Name Changes and Unique ID is same then no need to update
3. Only if Unique ID Changes then Update ACODE=6 and ADESC= M XYZ Where END_DATE =sysdate-1 and ACODE=2;
objective is to compare the previous row with current row and update if only UNIQUE ID changes.
so far i have written the above query. but it is wrongly updating. please advise.
|
|
10-27-11, 17:03
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
10-27-11, 17:51
|
|
Registered User
|
|
Join Date: Jul 2008
Posts: 80
|
|
|
|
sorry tonkuma.
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09014" with
level identifier "01050107".
Informational tokens are "DB2 v9.1.400.359", "s071028", "WR21396", and Fix Pack
"4".
|
|
10-27-11, 20:51
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
I got error message by your create table statement.
Code:
------------------------------ Commands Entered ------------------------------
create table temppp(uniqueid integer,employeeid varchar(15),skillid varchar(10),sname varchar(90),prof varchar2(10),acode varchar(1),
adesc varchar(30),start_date date,end_date date,rind varchar(1));
------------------------------------------------------------------------------
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "varchar2" was found following "ME varchar(90),
PROF ". Expected tokens may include: "VARCHAR". SQLSTATE=42601
Please copy and paste exactly what you did.
And, please write your result of "SELECT * FROM temppp".
(1) Before execution of your update statement.
(2) After execution of your update statement.
(3) Your expected/required result after update.
|
|
10-27-11, 22:46
|
|
Registered User
|
|
Join Date: Jul 2008
Posts: 80
|
|
Code:
drop table temppp
DB20000I The SQL command completed successfully.
create table temppp(uniqueid integer,employeeid varchar(15),skillid varchar(10),sname varchar(90),prof varchar(10),acode varchar(1), adesc varchar(30),start_date date,end_date date,rind varchar(1))
DB20000I The SQL command completed successfully.
insert into temppp values(3,10,'1','Unix','Basic','1','ABC',DATE(CURRENT DATE-1 DAY), DATE(CURRENT DATE-1 DAY),'N')
DB20000I The SQL command completed successfully.
insert into temppp values(4,10,'1','Unix Shell','Basic','2','XYZ',CURRENT DATE, NULL,'Y')
DB20000I The SQL command completed successfully.
insert into temppp values(5,10,'2','Pro*C','Basic','1','ABC',DATE(CURRENT DATE-1 DAY), DATE(CURRENT DATE-1 DAY),'N')
DB20000I The SQL command completed successfully.
insert into temppp values(5,10,'2','Pro*C','Expert','2','XYZ',CURRENT DATE, NULL,'Y')
DB20000I The SQL command completed successfully.
insert into temppp values(8,10,'5','Perl','Expert','1','XYZ',CURRENT DATE, NULL,'Y')
DB20000I The SQL command completed successfully.
insert into temppp values(1,10,'3','Java','Basic','1','ABC',DATE(CURRENT DATE-1 DAY), DATE(CURRENT DATE-1 DAY),'N')
DB20000I The SQL command completed successfully.
insert into temppp values(2,10,'3','Sun Java','Basic','2','XYZ',CURRENT DATE, NULL,'Y')
DB20000I The SQL command completed successfully.
insert into temppp values(3,11,'3','Java','Basic','1','ABC',DATE(CURRENT DATE-1 DAY), DATE(CURRENT DATE-1 DAY),'N')
DB20000I The SQL command completed successfully.
insert into temppp values(4,11,'3','Sun Java','Basic','2','XYZ',CURRENT DATE, NULL,'Y')
DB20000I The SQL command completed successfully.
insert into temppp values(1,11,'1','Unix','Basic','1','ABC',DATE(CURRENT DATE-1 DAY), DATE(CURRENT DATE-1 DAY),'N')
DB20000I The SQL command completed successfully.
insert into temppp values(2,11,'1','A Unix','Basic','2','XYZ',CURRENT DATE, NULL,'Y')
DB20000I The SQL command completed successfully.
insert into temppp values(1,12,'1','Unix','Basic','1','ABC',DATE(CURRENT DATE-1 DAY), DATE(CURRENT DATE-1 DAY),'N')
DB20000I The SQL command completed successfully.
insert into temppp values(2,12,'1','Unix','Basic','2','XYZ',CURRENT DATE, NULL,'Y')
DB20000I The SQL command completed successfully.
Update temppp g set g.acode='6', g.adesc='M XYZ' where (g.employeeid,g.uniqueid) in (Select f.employeeid,f.uniqueid from (select e.employeeid,e.uniqueid, rank() over(partition by e.employeeid,e.skillid order by e.uniqueid) as M_change, rank() over(partition by e.employeeid,e.skillid order by e.sname,e.prof) as B_change from (select a.* from temppp a where a.end_date=DATE(CURRENT DATE-1 DAY) and a.rind='N' union all select c.* from temppp c Where c.start_date=CURRENT DATE and c.acode='2' and c.rind='Y') e ) f where f.M_change=2 and f.B_change=1 ) and g.acode='2' and g.rind='Y'
DB20000I The SQL command completed successfully.
select * from temppp
UNIQUEID EMPLOYEEID SKILLID SNAME PROF ACODE ADESC START_DATE END_DATE RIND
----------- --------------- ---------- ------------------------------------------------------------------------------------------ ---------- ----- ------------------------------ ---------- ---------- ----
3 10 1 Unix Basic 1 ABC 10/26/2011 10/26/2011 N
4 10 1 Unix Shell Basic 2 XYZ 10/27/2011 - Y
5 10 2 Pro*C Basic 1 ABC 10/26/2011 10/26/2011 N
5 10 2 Pro*C Expert 2 XYZ 10/27/2011 - Y
8 10 5 Perl Expert 1 XYZ 10/27/2011 - Y
1 10 3 Java Basic 1 ABC 10/26/2011 10/26/2011 N
2 10 3 Sun Java Basic 2 XYZ 10/27/2011 - Y
3 11 3 Java Basic 1 ABC 10/26/2011 10/26/2011 N
4 11 3 Sun Java Basic 2 XYZ 10/27/2011 - Y
1 11 1 Unix Basic 1 ABC 10/26/2011 10/26/2011 N
2 11 1 A Unix Basic 6 M XYZ 10/27/2011 - Y
1 12 1 Unix Basic 1 ABC 10/26/2011 10/26/2011 N
2 12 1 Unix Basic 6 M XYZ 10/27/2011 - Y
13 record(s) selected.
Expected Result
UNIQUEID EMPLOYEEID SKILLID SNAME PROF ACODE ADESC START_DATE END_DATE RIND
----------- --------------- ---------- ------------------------------------------------------------------------------------------ ---------- ----- ------------------------------ ---------- ---------- ----
3 10 1 Unix Basic 1 ABC 10/26/2011 10/26/2011 N
4 10 1 Unix Shell Basic 2 XYZ 10/27/2011 - Y
5 10 2 Pro*C Basic 1 ABC 10/26/2011 10/26/2011 N
5 10 2 Pro*C Expert 2 XYZ 10/27/2011 - Y
8 10 5 Perl Expert 1 XYZ 10/27/2011 - Y
1 10 3 Java Basic 1 ABC 10/26/2011 10/26/2011 N
2 10 3 Sun Java Basic 2 XYZ 10/27/2011 - Y
3 11 3 Java Basic 1 ABC 10/26/2011 10/26/2011 N
4 11 3 Sun Java Basic 2 XYZ 10/27/2011 - Y
1 11 1 Unix Basic 1 ABC 10/26/2011 10/26/2011 N
2 11 1 A Unix Basic 2 XYZ 10/27/2011 - Y
1 12 1 Unix Basic 1 ABC 10/26/2011 10/26/2011 N
2 12 1 Unix Basic 6 M XYZ 10/27/2011 - Y
|
|
10-28-11, 03:45
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
First of all, those in your update statement may be not necessary.
a) UNION ALL
b) nested-subquery: "e"
c) qualifiers: "a", "c", "e", "f", and "g"
d) DATE function: ... where a.end_date=DATE(CURRENT DATE-1 DAY) and ...
Code:
/* Remove UNION ALL, nested-subquery "e" and qualifiers("a", "c", "e", "f", and "g"). */
Update temppp g
set acode = '6'
, adesc = 'M XYZ'
where (employeeid , uniqueid)
in (Select employeeid , uniqueid
from (select employeeid , uniqueid
, rank() over(partition by employeeid , skillid
order by uniqueid
) as M_change
, rank() over(partition by employeeid , skillid
order by sname , prof
) as B_change
from temppp
where end_date = CURRENT DATE - 1 DAY
and rind = 'N'
OR start_date = CURRENT DATE
and rind = 'Y'
and acode = '2'
) f
where M_change = 2
and B_change = 1
)
and acode = '2'
and rind = 'Y'
;
The reason of being updated extra row must be that B_change is reverse order to M_change in the group,
see last 6 rows with M_change and B_change.
Code:
UNIQUEID EMPLOYEEID SKILLID SNAME PROF ACODE ADESC START_DATE END_DATE RIND M_change B_change
----------- ---------- ---------- ---------- ---------- ----- ------ ---------- ---------- ---- -------- --------
3 11 3 Java Basic 1 ABC 10/26/2011 10/26/2011 N 1 1
4 11 3 Sun Java Basic 2 XYZ 10/27/2011 - Y 2 2
1 11 1 Unix Basic 1 ABC 10/26/2011 10/26/2011 N 1 2 <--- B_change is reverse order to M_change
2 11 1 A Unix Basic 6 M XYZ 10/27/2011 - Y 2 1 <--- B_change is reverse order to M_change
1 12 1 Unix Basic 1 ABC 10/26/2011 10/26/2011 N 1 1
2 12 1 Unix Basic 6 M XYZ 10/27/2011 - Y 2 1
A way to correct this issue may be to apply MAX olap specifications to M_change and B_change, like...
Example 1:
Code:
Update temppp g
set acode = '6'
, adesc = 'M XYZ'
where (employeeid , uniqueid)
in (Select employeeid , uniqueid
from (select employeeid , uniqueid
, MAX( rank() over(partition by employeeid , skillid
order by uniqueid
)
) OVER(partition by employeeid , skillid
) as M_change
, MAX( rank() over(partition by employeeid , skillid
order by sname , prof
)
) OVER(partition by employeeid , skillid
) as B_change
from temppp
where end_date = CURRENT DATE - 1 DAY
and rind = 'N'
OR start_date = CURRENT DATE
and rind = 'Y'
and acode = '2'
) f
where M_change = 2
and B_change = 1
)
and acode = '2'
and rind = 'Y'
;
Another example.
Example 2:
Code:
Update temppp g
set acode = '6'
, adesc = 'M XYZ'
where (employeeid , skillid)
in (Select employeeid , skillid
from temppp
where end_date = CURRENT DATE - 1 DAY
and rind = 'N'
OR start_date = CURRENT DATE
and rind = 'Y'
and acode = '2'
GROUP BY
employeeid , skillid
HAVING
MAX(uniqueid) > MIN(uniqueid)
AND MAX(sname) = MIN(sname)
)
and acode = '2'
and rind = 'Y'
;
|
Last edited by tonkuma; 10-28-11 at 07:43.
Reason: Number on sample codes. Add d) DATE function to not necessary items.
|
10-28-11, 07:48
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
This looks well on your supplied data.
Example 3:
Code:
------------------------------ Commands Entered ------------------------------
SELECT * FROM temppp;
------------------------------------------------------------------------------
UNIQUEID EMPLOYEEID SKILLID SNAME PROF ACODE ADESC START_DATE END_DATE RIND
----------- --------------- ---------- ------------------------------------------------------------------------------------------ ---------- ----- ------------------------------ ---------- ---------- ----
3 10 1 Unix Basic 1 ABC 2011-10-27 2011-10-27 N
4 10 1 Unix Shell Basic 2 XYZ 2011-10-28 - Y
5 10 2 Pro*C Basic 1 ABC 2011-10-27 2011-10-27 N
5 10 2 Pro*C Expert 2 XYZ 2011-10-28 - Y
8 10 5 Perl Expert 1 XYZ 2011-10-28 - Y
1 10 3 Java Basic 1 ABC 2011-10-27 2011-10-27 N
2 10 3 Sun Java Basic 2 XYZ 2011-10-28 - Y
3 11 3 Java Basic 1 ABC 2011-10-27 2011-10-27 N
4 11 3 Sun Java Basic 2 XYZ 2011-10-28 - Y
1 11 1 Unix Basic 1 ABC 2011-10-27 2011-10-27 N
2 11 1 A Unix Basic 2 XYZ 2011-10-28 - Y
1 12 1 Unix Basic 1 ABC 2011-10-27 2011-10-27 N
2 12 1 Unix Basic 2 XYZ 2011-10-28 - Y
13 record(s) selected.
------------------------------ Commands Entered ------------------------------
Update temppp t/*arget*/
set acode = '6'
, adesc = 'M XYZ'
where rind = 'Y'
and acode = '2'
AND start_date = CURRENT DATE
AND EXISTS
(Select 0
from temppp e/*xists*/
where e.end_date = CURRENT DATE - 1 DAY
and e.rind = 'N'
AND e.employeeid = t.employeeid
AND e.skillid = t.skillid
AND e.uniqueid <> t.uniqueid
AND e.sname = t.sname
)
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
SELECT * FROM temppp;
------------------------------------------------------------------------------
UNIQUEID EMPLOYEEID SKILLID SNAME PROF ACODE ADESC START_DATE END_DATE RIND
----------- --------------- ---------- ------------------------------------------------------------------------------------------ ---------- ----- ------------------------------ ---------- ---------- ----
3 10 1 Unix Basic 1 ABC 2011-10-27 2011-10-27 N
4 10 1 Unix Shell Basic 2 XYZ 2011-10-28 - Y
5 10 2 Pro*C Basic 1 ABC 2011-10-27 2011-10-27 N
5 10 2 Pro*C Expert 2 XYZ 2011-10-28 - Y
8 10 5 Perl Expert 1 XYZ 2011-10-28 - Y
1 10 3 Java Basic 1 ABC 2011-10-27 2011-10-27 N
2 10 3 Sun Java Basic 2 XYZ 2011-10-28 - Y
3 11 3 Java Basic 1 ABC 2011-10-27 2011-10-27 N
4 11 3 Sun Java Basic 2 XYZ 2011-10-28 - Y
1 11 1 Unix Basic 1 ABC 2011-10-27 2011-10-27 N
2 11 1 A Unix Basic 2 XYZ 2011-10-28 - Y
1 12 1 Unix Basic 1 ABC 2011-10-27 2011-10-27 N
2 12 1 Unix Basic 6 M XYZ 2011-10-28 - Y
13 record(s) selected.
|
Last edited by tonkuma; 10-28-11 at 12:15.
Reason: Add comment for correlation names in Update statement.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Update query for cdc?
