Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchy query help (oracle 12.1.0.2.0, Linux)
| Hierarchy query help [message #651011] |
Mon, 09 May 2016 15:13  |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi All,
Can someone please help me out on writing query for below.
I've below table with customer and address information. Creating surrogate key for every cust_id and addr_id combinations. If the customer_id is changed and if the updated customer_id/addr_id combination already exists on the table then pointing the old record to new record.
WITH CUST_ADDR_TBL AS
(SELECT 100 as IA_ID, 100 AS ACTIVE_IA_ID, 123 as CUST_ID, 100001 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-08' as UPDATE_DATE FROM DUAL
UNION
SELECT 200 as IA_ID, 200 AS ACTIVE_IA_ID, 234 as CUST_ID, 100002 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-07' as UPDATE_DATE FROM DUAL
UNION
SELECT 300 as IA_ID, 300 AS ACTIVE_IA_ID, 345 as CUST_ID, 100003 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-06' as UPDATE_DATE FROM DUAL
UNION
SELECT 400 as IA_ID, 400 AS ACTIVE_IA_ID, 456 as CUST_ID, 100001 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-06' as UPDATE_DATE FROM DUAL
)
Select * from cust_addr_tbl;
IA_ID ACTIVE_IA_ID CUST_ID ADDR_ID S UPDATE_DATE
---------- ------------ ---------- ---------- - -------------
100 100 123 100001 A 08-MAY-16
200 200 234 100002 A 07-MAY-16
300 300 345 100003 A 06-MAY-16
400 400 456 100001 A 06-MAY-16
4 rows selected.
Let's say if CUST_ID 456 has been changed to 123 then we update CUST_ID 456 to 123. After the update the table will have 2 records with cust_id 123 and addr_id 100001 so we inactivate one record and point that record other as below.
IA_ID ACTIVE_IA_ID CUST_ID ADDR_ID S UPDATE_DATE
---------- ------------ ---------- ---------- - -------------
100 100 123 100001 A 08-MAY-16
200 200 234 100002 A 07-MAY-16
300 300 345 100003 A 06-MAY-16
400 100 123 100001 I 06-MAY-16 <-- Status has been updated to I and the ACTIVE_IA_ID is now pointing to 100
Second table contains address updates which we get from some other team on monthly basis. We need to process this data and apply the results to the first table CUST_ADDR_TBL.
WITH ADDR_UPD_TBL AS
(SELECT 100 AS IA_ID, 123 as FINAL_CUST_ID, 100002 as FINAL_ADDR_ID, 'N' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
UNION
SELECT 200 AS IA_ID, 234 as FINAL_CUST_ID, 100002 as FINAL_ADDR_ID, '' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
UNION
SELECT 300 AS IA_ID, 345 as FINAL_CUST_ID, 100001 as FINAL_ADDR_ID, 'P' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
)
SELECT * FROM ADDR_UPD_TBL
IA_ID FINAL_CUST_ID FINAL_ADDR A AG_DATE
---------- ------------- ---------- - ---------
100 123 100002 N 09-MAY-16
200 234 100002 09-MAY-16
300 345 100001 P 09-MAY-16
I need to perform below. Need to process only addr_src in ('N', 'P') records from addr_upd_tbl table
1) If the cust_id/addr_id combination on the second table addr_upd_tbl already exists on cust_addr_tbl then use that record to update active_ia_id/status fields on cust_addr_tbl
2) If the cust_id/addr_id combination on second table not exists on cust_addr_tbl not exists on cust_addr_tbl then create new record in cust_addr_tbl
Let's take IA_ID 100 record : CUST_ADDR_TBL has cust_id 123 and addr_id 100001. In the second table addr_upd_tbl, addr_id as been changed to 100002 and this cust_id/addr_id combination is not on the cust_addr_tbl so need to create new IA_ID value for the cust_id/addr_id --> 123/100002 combination using sequence and load into cust_addr_tbl and inactive the 123/100001 record and point to newly created record as given below. Need output as below.
ORIG_IA_ID IA_ID ACTIVE_IA_ID CUST_ID ADDR_ID S UPDATE_DATE NEW_ADDR_ID
---------- ----- ------------ ---------- ---------- - ------------ ------------
100 100 1234 123 100001 I 08-MAY-16 100002 <-- Inactivate record and point active_ia_id field with IA_id [1234]of newly created record.
100 1234 1234 123 100002 A 08-MAY-16 <-- Need to create this new record for cust_id/addr_id 123/100002
300 300 1235 345 100003 I 06-MAY-16 100001 <-- Inactivate record and point active_ia_id field with IA_id [1235]of newly created record.
300 1235 1235 345 100001 A 06-MAY-16 <-- Need to create this new record for cust_id/addr_id 345/100001
Here is the sequence definition to generate new IA_ID's.
create sequence tst_addr_seq
start with 1234
increment by 1;
-- FUnction to generate sequeneces
CREATE OR REPLACE FUNCTION seq_demand
(p_seq_name IN VARCHAR2)
RETURN NUMBER
IS
v_seq_val NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual'
INTO v_seq_val;
RETURN v_seq_val;
END seq_demand;
I've written below SQL to check if the cust_id/addr_id combination from second table addr_upd_tbl exists in the first table cust_addr_tbl or not, but struggling on linking the records and updating the status and creating new records in cust_addr_tbl. Can someone please help me out.
WITH CUST_ADDR_TBL AS
(SELECT 100 as IA_ID, 100 AS ACTIVE_IA_ID, 123 as CUST_ID, 100001 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-08' as UPDATE_DATE FROM DUAL
UNION
SELECT 200 as IA_ID, 200 AS ACTIVE_IA_ID, 234 as CUST_ID, 100002 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-07' as UPDATE_DATE FROM DUAL
UNION
SELECT 300 as IA_ID, 300 AS ACTIVE_IA_ID, 345 as CUST_ID, 100003 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-06' as UPDATE_DATE FROM DUAL
UNION
SELECT 400 as IA_ID, 400 AS ACTIVE_IA_ID, 456 as CUST_ID, 100001 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-06' as UPDATE_DATE FROM DUAL
),
ADDR_UPD_TBL AS
(SELECT 100 AS IA_ID, 123 as FINAL_CUST_ID, 100002 as FINAL_GAID, 'N' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
UNION
SELECT 200 AS IA_ID, 234 as FINAL_CUST_ID, 100002 as FINAL_GAID, '' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
UNION
SELECT 300 AS IA_ID, 345 as FINAL_CUST_ID, 100001 as FINAL_GAID, 'P' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
)
SELECT wfa.IA_ID as orig_ia_id,
PC.IA_ID,
nvl(PC2.ACTIVE_IA_ID, seq_demand('tst_addr_seq') ) as ACTIVE_IA_ID,
nvl(PC2.STATUS_CODE, 'A') as STATUS,
wfa.FINAL_CUST_ID,
wfa.FINAL_GAID,
case
when PC.STATUS_CODE = 'I' then
'N'
else
decode(PC2.ACTIVE_IA_ID,null,'Y','N')
end as INSERT_FLAG
FROM ADDR_UPD_TBL wfa INNER JOIN CUST_ADDR_TBL PC ON (wfa.IA_ID = PC.IA_ID)
LEFT OUTER JOIN CUST_ADDR_TBL PC2 ON (WFA.FINAL_CUST_ID = PC2.CUST_ID and WFA.FINAL_GAID = PC2.ADDR_ID and PC2.STATUS_CODE = 'A')
WHERE wfa.ADDR_SRC in ('N', 'P')
ORDER BY 1;
ORIG_IA_ID IA_ID ACTIVE_IA_ID S FINAL_CUST_ID FINAL_GAID INSERT_FLAG
---------- ---------- ------------ - ------------- ---------- ------------
100 100 1207 A 123 100002 Y
300 300 1206 A 345 100001 Y
Appreciate your help!
Thanks
SS
|
|
|
|
| Re: Hierarchy query help [message #651016 is a reply to message #651011] |
Mon, 09 May 2016 20:29  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> CREATE TABLE CUST_ADDR_TBL AS
2 (SELECT 100 as IA_ID, 100 AS ACTIVE_IA_ID, 123 as CUST_ID, 100001 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-08' as UPDATE_DATE FROM DUAL
3 UNION
4 SELECT 200 as IA_ID, 200 AS ACTIVE_IA_ID, 234 as CUST_ID, 100002 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-07' as UPDATE_DATE FROM DUAL
5 UNION
6 SELECT 300 as IA_ID, 300 AS ACTIVE_IA_ID, 345 as CUST_ID, 100003 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-06' as UPDATE_DATE FROM DUAL
7 UNION
8 SELECT 400 as IA_ID, 400 AS ACTIVE_IA_ID, 456 as CUST_ID, 100001 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-06' as UPDATE_DATE FROM DUAL
9 )
10 /
Table created.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE ADDR_UPD_TBL AS
2 (SELECT 100 AS IA_ID, 123 as FINAL_CUST_ID, 100002 as FINAL_ADDR_ID, 'N' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
3 UNION
4 SELECT 200 AS IA_ID, 234 as FINAL_CUST_ID, 100002 as FINAL_ADDR_ID, '' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
5 UNION
6 SELECT 300 AS IA_ID, 345 as FINAL_CUST_ID, 100001 as FINAL_ADDR_ID, 'P' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
7 )
8 /
Table created.
SCOTT@orcl_12.1.0.2.0> create sequence tst_addr_seq start with 1234 increment by 1
2 /
Sequence created.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM cust_addr_tbl
2 /
IA_ID ACTIVE_IA_ID CUST_ID ADDR_ID S UPDATE_DATE
---------- ------------ ---------- ---------- - ---------------
100 100 123 100001 A Sun 08-May-2016
200 200 234 100002 A Sat 07-May-2016
300 300 345 100003 A Fri 06-May-2016
400 400 456 100001 A Fri 06-May-2016
4 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM addr_upd_tbl
2 /
IA_ID FINAL_CUST_ID FINAL_ADDR_ID A AG_DATE
---------- ------------- ------------- - ---------------
100 123 100002 N Mon 09-May-2016
200 234 100002 Mon 09-May-2016
300 345 100001 P Mon 09-May-2016
3 rows selected.
SCOTT@orcl_12.1.0.2.0> DECLARE
2 v_seq_val NUMBER;
3 BEGIN
4 FOR u IN
5 (SELECT ia_id, final_cust_id, final_addr_id, ag_date
6 FROM addr_upd_tbl
7 WHERE addr_src IN ('N', 'P'))
8 LOOP
9 SELECT tst_addr_seq.NEXTVAL INTO v_seq_val FROM DUAL;
10 UPDATE cust_addr_tbl o
11 SET o.active_ia_id = v_seq_val,
12 o.status_code = 'I',
13 o.update_date = u.ag_date
14 WHERE o.ia_id = u.ia_id
15 AND o.status_code = 'A';
16 INSERT INTO cust_addr_tbl (ia_id, active_ia_id, cust_id, addr_id, status_code, update_date)
17 VALUES (v_seq_val, v_seq_val, u.final_cust_id, u.final_addr_id, 'A', u.ag_date);
18 END LOOP;
19 END;
20 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM cust_addr_tbl
2 /
IA_ID ACTIVE_IA_ID CUST_ID ADDR_ID S UPDATE_DATE
---------- ------------ ---------- ---------- - ---------------
100 1234 123 100001 I Mon 09-May-2016
200 200 234 100002 A Sat 07-May-2016
300 1235 345 100003 I Mon 09-May-2016
400 400 456 100001 A Fri 06-May-2016
1234 1234 123 100002 A Mon 09-May-2016
1235 1235 345 100001 A Mon 09-May-2016
6 rows selected.
[Updated on: Mon, 09 May 2016 20:36] Report message to a moderator |
|
|
|
| Re: Hierarchy query help [message #651052 is a reply to message #651016] |
Tue, 10 May 2016 09:27 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Thank you so much Barbara for your answer!
If the cust_id/addr_id combination from addr_upd_tbl table already exists on the cust_addr_tbl then i don't need to insert new record in the cust_addr_tbl instead update the existing record by pointing to the cust_id/addr_id combination received in the addr_upd_tbl as given below.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE CUST_ADDR_TBL AS
(SELECT 100 as IA_ID, 100 AS ACTIVE_IA_ID, 123 as CUST_ID, 100001 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-08' as UPDATE_DATE FROM DUAL
UNION
SELECT 1234 as IA_ID, 1234 AS ACTIVE_IA_ID, 123 as CUST_ID, 100002 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-09' as UPDATE_DATE FROM DUAL
UNION
SELECT 200 as IA_ID, 200 AS ACTIVE_IA_ID, 234 as CUST_ID, 100002 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-07' as UPDATE_DATE FROM DUAL
UNION
SELECT 300 as IA_ID, 300 AS ACTIVE_IA_ID, 345 as CUST_ID, 100003 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-06' as UPDATE_DATE FROM DUAL
UNION
SELECT 400 as IA_ID, 400 AS ACTIVE_IA_ID, 456 as CUST_ID, 100001 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-06' as UPDATE_DATE FROM DUAL
)
;
Table created.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE ADDR_UPD_TBL AS
(SELECT 100 AS IA_ID, 123 as FINAL_CUST_ID, 100002 as FINAL_ADDR_ID, 'N' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
UNION
SELECT 200 AS IA_ID, 234 as FINAL_CUST_ID, 100002 as FINAL_ADDR_ID, '' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
UNION
SELECT 300 AS IA_ID, 345 as FINAL_CUST_ID, 100001 as FINAL_ADDR_ID, 'P' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
UNION
SELECT 1234 AS IA_ID, 123 as FINAL_CUST_ID, 100002 as FINAL_ADDR_ID, '' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
)
/
Table created.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM cust_addr_tbl
2 /
IA_ID ACTIVE_IA_ID CUST_ID ADDR_ID S UPDATE_DATE
---------- ------------ ---------- ---------- - ---------------
100 100 123 100001 A Sun 08-May-2016
1234 1234 123 100002 A Mon 09-May-2016
200 200 234 100002 A Sat 07-May-2016
300 300 345 100003 A Fri 06-May-2016
400 400 456 100001 A Fri 06-May-2016
5 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM addr_upd_tbl
2 /
IA_ID FINAL_CUST_ID FINAL_ADDR_ID A AG_DATE
---------- ------------- ------------- - ---------------
100 123 100002 N Mon 09-May-2016 <-- This cust/addr_id combination already exists in cust_addr_tbl with IA_ID 1234 so need to point IA_ID 100 to 1234
200 234 100002 Mon 09-May-2016
300 345 100001 P Mon 09-May-2016
1234 123 100002 Mon 09-May-2016
4 rows selected.
Cust_Id/Addr_Id combination for IA_ID 100 has the addr_src as 'N' and this combination 123/100002 already exists on the cust_addr_tbl with IA_ID 1234. So now IA_ID 100 need to point to IA_ID 1234 instead of creating new IA_ID and update status_code to 'I' for IA_ID 100 record.
Appreciate your help!
Thanks
SS
|
|
|
|
|
|
| Re: Hierarchy query help [message #651066 is a reply to message #651065] |
Tue, 10 May 2016 12:50 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi Barbara,
Sorry for not providing the required output. Here is what i need after the update and insert.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE CUST_ADDR_TBL AS
(SELECT 100 as IA_ID, 100 AS ACTIVE_IA_ID, 123 as CUST_ID, 100001 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-08' as UPDATE_DATE, CAST(NULL as NUMBER) as NEW_ADDR_ID FROM DUAL
UNION
SELECT 1234 as IA_ID, 1234 AS ACTIVE_IA_ID, 123 as CUST_ID, 100002 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-09' as UPDATE_DATE, CAST(NULL as NUMBER) as NEW_ADDR_ID FROM DUAL
UNION
SELECT 200 as IA_ID, 200 AS ACTIVE_IA_ID, 234 as CUST_ID, 100002 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-07' as UPDATE_DATE, CAST(NULL as NUMBER) as NEW_ADDR_ID FROM DUAL
UNION
SELECT 300 as IA_ID, 300 AS ACTIVE_IA_ID, 345 as CUST_ID, 100003 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-06' as UPDATE_DATE, CAST(NULL as NUMBER) as NEW_ADDR_ID FROM DUAL
UNION
SELECT 400 as IA_ID, 400 AS ACTIVE_IA_ID, 456 as CUST_ID, 100001 as ADDR_ID, 'A' as STATUS_CODE, DATE '2016-05-06' as UPDATE_DATE, CAST(NULL as NUMBER) as NEW_ADDR_ID FROM DUAL
)
;
Table created.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE ADDR_UPD_TBL AS
(SELECT 100 AS IA_ID, 123 as FINAL_CUST_ID, 100002 as FINAL_ADDR_ID, 'N' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
UNION
SELECT 200 AS IA_ID, 234 as FINAL_CUST_ID, 100002 as FINAL_ADDR_ID, '' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
UNION
SELECT 300 AS IA_ID, 345 as FINAL_CUST_ID, 100001 as FINAL_ADDR_ID, 'P' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
UNION
SELECT 1234 AS IA_ID, 123 as FINAL_CUST_ID, 100002 as FINAL_ADDR_ID, '' as ADDR_SRC, DATE '2016-05-09' as AG_DATE FROM DUAL
)
/
Table created.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM cust_addr_tbl
2 /
IA_ID ACTIVE_IA_ID CUST_ID ADDR_ID S UPDATE_DATE NEW_ADDR_ID
---------- ------------ ---------- ---------- - --------------- -------------
100 100 123 100001 A Sun 08-May-2016
1234 1234 123 100002 A Mon 09-May-2016
200 200 234 100002 A Sat 07-May-2016
300 300 345 100003 A Fri 06-May-2016
400 400 456 100001 A Fri 06-May-2016
5 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM addr_upd_tbl
2 /
IA_ID FINAL_CUST_ID FINAL_ADDR_ID A AG_DATE
---------- ------------- ------------- - ---------------
100 123 100002 N Mon 09-May-2016 <-- This cust/addr_id combination already exists in cust_addr_tbl with IA_ID 1234 so need to point IA_ID 100 to 1234
200 234 100002 Mon 09-May-2016
300 345 100001 P Mon 09-May-2016
1234 123 100002 Mon 09-May-2016
4 rows selected.
Required Output after update and insert:
IA_ID ACTIVE_IA_ID CUST_ID ADDR_ID S UPDATE_DATE NEW_ADDR_ID
---------- ------------ ---------- ---------- - --------------- -------------
100 1234 123 100001 I Mon 09-May-2016 100002
1234 1234 123 100002 A Mon 09-May-2016
200 200 234 100002 A Sat 07-May-2016
300 1235 345 100003 I Mon 09-May-2016 100001
400 400 456 100001 A Fri 06-May-2016
1235 1235 345 100001 A Mon 09-May-2016
Thanks
SS
|
|
|
|
| Re: Hierarchy query help [message #651071 is a reply to message #651066] |
Tue, 10 May 2016 14:14 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> SELECT * FROM cust_addr_tbl
2 /
IA_ID ACTIVE_IA_ID CUST_ID ADDR_ID S UPDATE_DATE NEW_ADDR_ID
---------- ------------ ---------- ---------- - --------------- -----------
100 100 123 100001 A Sun 08-May-2016
200 200 234 100002 A Sat 07-May-2016
300 300 345 100003 A Fri 06-May-2016
400 400 456 100001 A Fri 06-May-2016
1234 1234 123 100002 A Mon 09-May-2016
5 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM addr_upd_tbl
2 /
IA_ID FINAL_CUST_ID FINAL_ADDR_ID A AG_DATE
---------- ------------- ------------- - ---------------
100 123 100002 N Mon 09-May-2016
200 234 100002 Mon 09-May-2016
300 345 100001 P Mon 09-May-2016
1234 123 100002 Mon 09-May-2016
4 rows selected.
SCOTT@orcl_12.1.0.2.0> create sequence tst_addr_seq start with 1235 increment by 1
2 /
Sequence created.
SCOTT@orcl_12.1.0.2.0> DECLARE
2 v_ia_id NUMBER;
3 BEGIN
4 FOR u IN
5 (SELECT *
6 FROM addr_upd_tbl
7 WHERE addr_src IN ('N', 'P'))
8 LOOP
9 BEGIN
10 SELECT ia_id
11 INTO v_ia_id
12 FROM cust_addr_tbl
13 WHERE cust_id = u.final_cust_id
14 AND addr_id = u.final_addr_id
15 AND status_code = 'A';
16 EXCEPTION
17 WHEN NO_DATA_FOUND THEN
18 SELECT tst_addr_seq.NEXTVAL INTO v_ia_id FROM DUAL;
19 INSERT INTO cust_addr_tbl (ia_id, active_ia_id, cust_id, addr_id, status_code, update_date)
20 VALUES (v_ia_id, v_ia_id, u.final_cust_id, u.final_addr_id, 'A', u.ag_date);
21 END;
22 UPDATE cust_addr_tbl
23 SET active_ia_id = v_ia_id,
24 cust_id = u.final_cust_id,
25 status_code = 'I',
26 update_date = u.ag_date,
27 new_addr_id = u.final_addr_id
28 WHERE ia_id = u.ia_id
29 AND status_code = 'A';
30 END LOOP;
31 END;
32 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM cust_addr_tbl
2 /
IA_ID ACTIVE_IA_ID CUST_ID ADDR_ID S UPDATE_DATE NEW_ADDR_ID
---------- ------------ ---------- ---------- - --------------- -----------
100 1234 123 100001 I Mon 09-May-2016 100002
200 200 234 100002 A Sat 07-May-2016
300 1235 345 100003 I Mon 09-May-2016 100001
400 400 456 100001 A Fri 06-May-2016
1234 1234 123 100002 A Mon 09-May-2016
1235 1235 345 100001 A Mon 09-May-2016
6 rows selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 19:23:54 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
