Home » SQL & PL/SQL » SQL & PL/SQL » Help me - Data Fix
Help me - Data Fix [message #270781] Fri, 28 September 2007 00:42 Go to next message
thandavakarumuri
Messages: 64
Registered: May 2005
Member
Hi Experts,

I was asked to do some data fix. I am in confusion
how to fix it.

Actually i have 4 different cases.

I am using oracle 9.2.0.8.0 version

See the table properties in code:
(FYI both the tables are not same. Only the posted fields
are same in two tables. There is a requirement that,
complete this data fix and truncate the source table
for new processing of load. This is a monthly table.
Every month the source table will be truncated
and re-used.)

Now lets say Table_A is the association/source table
and Table_b is a target table (which needs to be
updated by matching the mem_id in both tables).

                   
columns for Table_A 
------------------- 
mem_id number
ind_id number
ent_id number
.
.

columns for Table_B 
-------------------
mem_id number
ind_id number
ent_id number
.
.

4 different conditions/cases:
1) for mem_id: 1000210 has one row in table_a and 
table_b matching mem_id. 
but ind_id and ent_id are null

Ex: Table_a has  rows
-------------------

mem_id    ind_id   ent_id
------    ------   ------
29899890  382      89999

Table_b has  rows
-------------------
mem_id    ind_id   ent_id
------    ------   ------
29899890  

(I hope this can be done with a small plsql block)
 
2) for mem_id: 1200090 has one row in table_a 
   and table_b has two rows. 
   In table_b, 1 row is with null ind_id and ent_ids.

Ex: Table_a has  rows
-------------------
mem_id    ind_id   ent_id
------    ------   ------
29899890  382      89999

Table_b has  rows
-------------------

mem_id    ind_id   ent_id
------    ------   ------
29899890  null     null  (The 382 can be matched here)
29899890  372      89999 
 
3) for mem_id: 1320900 has two rows in table_a. 
2diff rows with diff ind_id but with same ent_ids.  
table_b has two rows. 1 row is with null ind_id and ent_ids. 
the 1 null ind_id can be matched with least ind_id of the table_a.

Ex: Table_a has  rows
-------------------

mem_id    ind_id   ent_id
------    ------   ------
29899890  382      89999
29899890  372      89999

Table_b has  rows
-------------------
mem_id    ind_id   ent_id
------    ------   ------
29899890  null     null  (The 382 can be matched here)
29899890  372      89999 

4) for mem_id : 29899890 table_a has 2 rows with different 
ind_id and ent_ids.( no nulls) table_b has 3 rows. 
with 2same ind_id rows and one null ind_id. 
the 1 null ind_id can be matched which is not 
existing out of the two in table_a. 

Ex: Table_a has  rows
-------------------

mem_id    ind_id   ent_id
------    ------   ------
29899890  382      89999
29899890  372      89999

Table_b has  rows
-------------------
mem_id    ind_id   ent_id
------    ------   ------
29899890  null     null  (The 382 can be matched here)
29899890  372      89999 
29899890  372      89999



This is actually a small example. I have to perform
this data fix for 1million records. Please suggest me
how can i complete this? I have to fix this issue
and deploy it by Tuesday (CST).

Am actually planning to use MERGE for 2,3 and 4.
But am not able to achive it. Below is my trial
query. I have to update all the null ind_id's
and ent_id's.

MERGE INTO table_b M
   USING (SELECT mem_id, ind_id, ent_id 
          FROM table_a
          WHERE ind_id = 352) H
   ON (M.mem_id = H.mem_id)
   WHEN MATCHED THEN UPDATE SET M.ind_id = H.ind_id
   WHEN MATCHED THEN UPDATE SET M.ENTITY = H.ent_id
                     WHERE ind_id IS NULL 
                     AND ent_id IS NULL; 

MERGE INTO table_b M
   USING (SELECT mem_id, ind_id, ent_id 
          FROM table_a
          WHERE ind_id = 352) H
   ON (M.mem_id = H.mem_id)
   WHEN MATCHED THEN UPDATE SET M.ind_id = H.ind_id,
                                M.ENTITY = H.ent_id
                     WHERE ind_id IS NULL 
                     AND ent_id IS NULL;

MERGE INTO table_b M
   USING (SELECT mem_id, ind_id, ent_id 
          FROM table_a
          WHERE ind_id = 352) H
   ON (M.mem_id = H.mem_id)
   WHEN MATCHED THEN UPDATE SET M.ind_id = H.ind_id
   WHEN MATCHED THEN UPDATE SET M.ENTITY = H.ent_id
                     WHERE ind_id IS NULL 
                     AND ent_id IS NULL;
                     
MERGE INTO table_b M
   USING (SELECT HR.mem_id,HR. ind_id,HR. ent_id 
          FROM table_a HR
          WHERE HR.mem_id = M.mem_id
          ) H
   ON (H.ind_id = M.ind_id)
   WHEN NOT MATCHED THEN UPDATE SET M.ent_id = H.ent_id
                         WHERE ent_id IS NULL 
   WHEN NOT MATCHED THEN UPDATE SET M.ind_id = H.ind_id                         
                     AND ind_id IS NULL; 



Please help me in fixing this issue/data fix.
Please explain me how can i fix this null ind_id's
and ent_id's. Plz suggest me any easiest option.

Thanks a lot in advance,
Tandava

[Updated on: Fri, 28 September 2007 11:19]

Report message to a moderator

Re: Help me - Data Fix [message #270791 is a reply to message #270781] Fri, 28 September 2007 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Could you modify your post accordingly to Guide: Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
And also post your Oracle version (4 decimals) as requested.

Regards
Michel
Re: Help me - Data Fix [message #270802 is a reply to message #270791] Fri, 28 September 2007 01:19 Go to previous messageGo to next message
thandavakarumuri
Messages: 64
Registered: May 2005
Member
Yes i have made the changes and mentioned the oracle version i am using. Please help me now.
Re: Help me - Data Fix [message #273200 is a reply to message #270791] Tue, 09 October 2007 08:14 Go to previous messageGo to next message
thandavakarumuri
Messages: 64
Registered: May 2005
Member
Hi Michel,

Greetings for the day!

I still did not receive the reply for my request. Could you please help me?

Thanks,
Tandava
Re: Help me - Data Fix [message #273229 is a reply to message #273200] Tue, 09 October 2007 10:56 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case, create table and insert statements along with the result you expect with this test case and then maybe I have a look.

Regards
Michel
Previous Topic: How to check if list partition exist
Next Topic: Identifying parent data source in downstream application
Goto Forum:
  


Current Time: Thu Dec 08 02:04:07 CST 2016

Total time taken to generate the page: 0.08593 seconds