I have two tables (target & source) which have the same structure. Target has a subset of accounts & telephone info from the source table. Source has duplicate account # with different contact information in each row.
I’m attempting to update the target table contact information so that a single row / account number has all contact information; additional contact info goes into extended columns on the target table. Keying on the account #, I would like to compare source telephone information to see if it exists in the target table, if it doesn’t exist, add the information in the extended columns as in the example below
REF Account No Cell Site Contact
SRC 3000030 1112374016
SRC 3000030 2226883696
SRC 3000030 3334023239
SRC 3000030 4442548632
TGT 3000030 1112374016
Target Table
Account No Cell Site Contact Phone5 Phone6 Phone7 Phone8
3000030 1112374016
All non duplicate contact numbers go into these over flow columns Through some research & help from StackOverflow, I crafted a View with a UNION ALL, and a Merge Statement with a Pivot table:
CREATE or REPLACE VIEW col_combine_v ( accountno, cell, site, contact, email) AS select accountno, cell, site, contact, email1 from target union all
select accountno, cell, site, contact, email1 from source order by accountno desc;
MERGE INTO target tgt
USING (select accountno, r1_cell, r1_site, r1_contact, r1_email, r2_cell, r2_site, r2_contact, r2_email
from (select s.*,row_number()over(partition by accountno order by accountno) rn
from col_combine_v s
)
pivot (
min(cell) cell,min(site) site, min(contact) contact, min(email) email
FOR rn
IN (1 R1,2 R2)
)
) src
ON (src.accountno = tgt.accountno)
WHEN MATCHED
THEN
UPDATE SET
tgt.phone5 =
CASE
WHEN src.r1_contact <> src.r2_contact
AND src.accountno = tgt.accountno
THEN src.r1_contact
END,
tgt.phone6 =
CASE
WHEN src.r1_contact <> src.r2_contact
AND src.r1_contact <> tgt.phone6
AND src.accountno = tgt.accountno
THEN src.r1_contact
END
Unfortunately, after I perform the PIVOT, I don’t see any of the other contact numbers except for the next to the last one (4442548632). This is the only number that gets updated on the Target table. But I need to extract all the telephone numbers that don’t exist in the Target table.
I’m relatively new to Oracle / SQL and appreciate your help and guidance.
7/14/20: I have updated the LiveSQL link previously provided in a attempt to mimic as much as possible the use case I'm trying to solve.
I created a view to return 3 rows of data(1). If I select the data from the view, it returns 3 rows of data, (3). Once I create a Pivot table from the view, only two rows are return, (2). I need access to all the data in the view so I can update the target table accordingly.
Basically I'm trying to determine if the Source "Contact" information exists in the Target table and if it doesn't exist, put the Source data in extended columns, i.e. phone4, phone5, etc. Note that the Target columns cell, site, contact are duplicates of the Source table equivalent columns.
It's my understanding that I can't compare two tables independently because I have no assurances that the row numbers are the same.
Essentially how does one compare two tables (A & B) so that I can use data in Table A to update the data in Table B based on some conditions.
Thank you
I'm still not entirely sure of your requirement (thats me not you :-)) but hopefully this will assist.
My logic is:
- transpose existing targets to rows
- combine with source to find existing matches
- pivot back to a row per account
- use that as input to merge with the target
SQL>
SQL> Create TABLE source (accountno integer, cell number, site number, contact number);
Table created.
SQL> Insert INTO source (accountno, cell, site, contact) Values (3000030,1 ,2 ,1112374016);
1 row created.
SQL> Insert INTO source (accountno, cell, site, contact) Values (3000030,1 ,2 ,2226883696);
1 row created.
SQL> Insert INTO source (accountno, cell, site, contact) Values (3000030,1 ,2 ,3334023239);
1 row created.
SQL> Insert INTO source (accountno, cell, site, contact) Values (3000030,1 ,2 ,4442548632);
1 row created.
SQL>
SQL> Insert INTO source (accountno, cell, site, contact) Values (3000031,1 ,2 ,1112374016);
1 row created.
SQL> Insert INTO source (accountno, cell, site, contact) Values (3000031,1 ,2 ,2226883696);
1 row created.
SQL> Insert INTO source (accountno, cell, site, contact) Values (3000031,1 ,2 ,3334023239);
1 row created.
SQL> Insert INTO source (accountno, cell, site, contact) Values (3000031,1 ,2 ,4442548632);
1 row created.
SQL>
SQL> Create TABLE target (accountno integer, cell number, site number,
2 contact number, phone4 number, phone5 number, phone6 number);
Table created.
SQL> Insert INTO target (accountno, cell, site, contact) Values (3000030,1 ,2 ,1112374016);
1 row created.
SQL>
SQL> with target_rows as
2 (
3 select ACCOUNTNO , CELL , SITE , 1 PHONE_IDX, contact PHONE
4 from target
5 union all
6 select ACCOUNTNO , CELL , SITE , PHONE_IDX, PHONE from target
7 unpivot ( phone for phone_idx in ( phone4 as 2, phone5 as 3,phone6 as 4) )
8 ),
9 candidates as
10 (
11 select s.ACCOUNTNO, s.CELL, s.SITE, nvl(t.phone, s.CONTACT) contact,
12 max(t.phone) over ( partition by s.accountno) tag,
13 row_number() over ( partition by s.accountno order by t.phone ) as seq
14 from source s, target_rows t
15 where s.accountno=t.accountno(+)
16 and s.cell=t.cell(+)
17 and s.site=t.site(+)
18 and s.contact=t.phone(+)
19 ),
20 transposed as
21 (
22 select *
23 from candidates
24 pivot
25 ( min(contact) for seq in (1 as P1, 2 as P2, 3 as P3, 4 as P4)
26 )
27 )
28 select * from transposed;
ACCOUNTNO CELL SITE TAG P1 P2 P3 P4
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
3000030 1 2 1112374016 1112374016 3334023239 2226883696 4442548632
3000031 1 2 1112374016 2226883696 3334023239 4442548632
SQL>
SQL>
So that final result is now a potential source for your merge command.