Login or Register to Ask a Question and Join Our Community


Programming

Help with mapping two tables and filling the null values

Login or Register to Reply

 
Thread Tools Search this Thread
Top Forums Programming Help with mapping two tables and filling the null values
# 1  
Old 01-18-2012
Help with mapping two tables and filling the null values
Hi All ,

I have two tables. I will provide sample data in the tables below.

table1:

dept_id dept_name rep_id admin_lastname
10 dept of int.medicine Paul
10 dept of int.medicine Frank
20 dept of chemistry Young
20 dept of chemistry Bill
30 school of denistry kaufmann
40 research dept kapoor
40 research dept chen

table2

dept_id dept_name rep_id
10 dept of int.medicine A-M 05
10 dept of int.medicine N-Z 08
20 dept of chemistry A-G 04
20 dept of chemistry H-Z 01
30 school of denistry 10
40 research dept A-K 09
40 research dept L-Z 07

now I have to get the values for the rep_id in table1. For that I have to use the table 2. the condition is if the first character of the admin_lastname in table 1 is in the range (like A-M OR A-G OR N-Z OR A-K OR L-Z in the dept_name in table2) depending on that I will have to use the dept_id and rep_id in table2.

Please let me know how can i write a sql code in oracle to accomplish this .
# 2  
Old 01-18-2012
Quote:
Originally Posted by megha2525
...
I have two tables. I will provide sample data in the tables below.
table1:
dept_id dept_name rep_id admin_lastname
10 dept of int.medicine Paul
10 dept of int.medicine Frank
20 dept of chemistry Young
20 dept of chemistry Bill
30 school of denistry kaufmann
40 research dept kapoor
40 research dept chen

table2

dept_id dept_name rep_id
10 dept of int.medicine A-M 05
10 dept of int.medicine N-Z 08
20 dept of chemistry A-G 04
20 dept of chemistry H-Z 01
30 school of denistry 10
40 research dept A-K 09
40 research dept L-Z 07
now I have to get the values for the rep_id in table1. For that I have to use the table 2. the condition is if the first character of the admin_lastname in table 1 is in the range (like A-M OR A-G OR N-Z OR A-K OR L-Z in the dept_name in table2) depending on that I will have to use the dept_id and rep_id in table2.
...
Here's an update statement that should do the trick -

Code:
SQL>
SQL> -- Before update
SQL> select * from table1;
 
DEPT_ID DEPT_NAME                 REP_ID  ADMIN_LASTNAME
---------- ------------------------- ------- --------------------
     10 dept of int.medicine              Paul
     10 dept of int.medicine              Frank
     20 dept of chemistry                 Young
     20 dept of chemistry                 Bill
     30 school of denistry                kaufmann
     40 research dept                     kapoor
     40 research dept                     chen
 
7 rows selected.
 
SQL>
SQL> select * from table2;
DEPT_ID DEPT_NAME                 REP_ID
---------- ------------------------- -------
     10 dept of int.medicine A-M  05
     10 dept of int.medicine N-Z  08
     20 dept of chemistry A-G     04
     20 dept of chemistry H-Z     01
     30 school of denistry        10
     40 research dept A-K         09
     40 research dept L-Z         07
 
7 rows selected.
 
SQL>
SQL> -- Update statement
SQL> UPDATE table1 x
2     SET x.rep_id =
3           (SELECT y.rep_id
4              FROM table2 y
5             WHERE y.dept_id = x.dept_id
6               AND UPPER (SUBSTR (x.admin_lastname, 1, 1))
7                     BETWEEN CASE INSTR (y.dept_name, '-', -1)
8                               WHEN 0 THEN 'A'
9                               ELSE SUBSTR (y.dept_name, INSTR (y.dept_name, '-', -1) - 1, 1)
10                             END
11                         AND CASE INSTR (y.dept_name, '-', -1)
12                               WHEN 0 THEN 'Z'
13                               ELSE SUBSTR (y.dept_name, INSTR (y.dept_name, '-', -1) + 1, 1)
14                             END
15           )
16  /
 
7 rows updated.
 
SQL>
SQL> -- After update
SQL> select * from table1;
 
DEPT_ID DEPT_NAME                 REP_ID  ADMIN_LASTNAME
---------- ------------------------- ------- --------------------
     10 dept of int.medicine      08      Paul
     10 dept of int.medicine      05      Frank
     20 dept of chemistry         01      Young
     20 dept of chemistry         04      Bill
     30 school of denistry        10      kaufmann
     40 research dept             09      kapoor
     40 research dept             09      chen
 
7 rows selected.
 
SQL>
SQL>
SQL>

tyler_durden

---------- Post updated at 06:37 PM ---------- Previous update was at 06:06 PM ----------

And in case you have Oracle version 10g or higher, you could make use of Regular Expressions -

Code:
SQL>
SQL> -- Before update
SQL> select * from table1;
 
 DEPT_ID DEPT_NAME                 REP_ID  ADMIN_LASTNAME
---------- ------------------------- ------- --------------------
      10 dept of int.medicine              Paul
      10 dept of int.medicine              Frank
      20 dept of chemistry                 Young
      20 dept of chemistry                 Bill
      30 school of denistry                kaufmann
      40 research dept                     kapoor
      40 research dept                     chen
 
7 rows selected.
 
SQL>
SQL> select * from table2;
 
 DEPT_ID DEPT_NAME                 REP_ID
---------- ------------------------- -------
      10 dept of int.medicine A-M  05
      10 dept of int.medicine N-Z  08
      20 dept of chemistry A-G     04
      20 dept of chemistry H-Z     01
      30 school of denistry        10
      40 research dept A-K         09
      40 research dept L-Z         07
 
7 rows selected.
 
SQL>
SQL> --
SQL> UPDATE table1 x
2     SET x.rep_id =
3           (
4            SELECT y.rep_id
5              FROM table2 y
6             WHERE y.dept_id = x.dept_id
7               AND UPPER (REGEXP_REPLACE (x.admin_lastname, '^(.).*$', '\1'))
8                     BETWEEN CASE
9                               WHEN REGEXP_SUBSTR (y.dept_name, '-') = '-'
10                               THEN REGEXP_REPLACE (y.dept_name, '^.*(.)-.*$', '\1')
11                               ELSE 'A'
12                             END
13                         AND
14                             CASE
15                               WHEN REGEXP_SUBSTR (y.dept_name, '-') = '-'
16                               THEN REGEXP_REPLACE (y.dept_name, '^.*-(.).*$', '\1')
17                               ELSE 'Z'
18                             END
19           )
20  /
 
7 rows updated.
 
SQL>
SQL> -- After update
SQL> select * from table1;
 
 DEPT_ID DEPT_NAME                 REP_ID  ADMIN_LASTNAME
---------- ------------------------- ------- --------------------
      10 dept of int.medicine      08      Paul
      10 dept of int.medicine      05      Frank
      20 dept of chemistry         01      Young
      20 dept of chemistry         04      Bill
      30 school of denistry        10      kaufmann
      40 research dept             09      kapoor
      40 research dept             09      chen
 
7 rows selected.
 
SQL>
SQL>
SQL>

The instr/substr approach should work across all versions of Oracle (as long as they aren't from the Jurassic age).

tyler_durden
Last edited by durden_tyler; 01-22-2012 at 06:32 PM..
This User Gave Thanks to durden_tyler For This Post:
megha2525
# 3  
Old 01-19-2012
Thank you so much durden_tyler.
But I forgot to mention something. Apologise for not mentioning it before.
table1 that I referred to in my example is not a table. Its a cursor .I am getting data into this cursor from a join of other tables .
Also , the rep_id this cursor is not always null. Only when there are null values for this rep_id in the cursor , thats when we need to fetch the rep_id from table2 .

the main columns in the cursor are main_id , item_id, dept_id .
The primary key is set on main_id and item_id on the table from where I pull the data into the cursor.

All that i am trying to do is to spool the data from the cursor.
So, when the rep_id is null in this cursor thats when we need to get the data from table2 based on the condition .(like first character of admin_last name is in a specific range ) .

Please help to resolve this issue.
# 4  
Old 01-20-2012
Quote:
Originally Posted by megha2525
...
table1 that I referred to in my example is not a table. Its a cursor .I am getting data into this cursor from a join of other tables .
Also , the rep_id this cursor is not always null. Only when there are null values for this rep_id in the cursor , thats when we need to fetch the rep_id from table2 .

the main columns in the cursor are main_id , item_id, dept_id .
The primary key is set on main_id and item_id on the table from where I pull the data into the cursor.

All that i am trying to do is to spool the data from the cursor.
So, when the rep_id is null in this cursor thats when we need to get the data from table2 based on the condition .(like first character of admin_last name is in a specific range ). ...
Perform a left outer join of your cursor with table2 on the conditions specified in my earlier post. Use the nvl2 function to fill in "gaps" in rep_id column.

tyler_durden
This User Gave Thanks to durden_tyler For This Post:
megha2525
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Mapping the values of ids of two columns of file1 from file2

I have of two space separated files: ==> File1 <== PT|np_496075.1 st|K92748.1 st|K89648.1 PT|np_001300561.1 PT|np_497284.1 st|K90752.1 st|K90279.1 PT|np_740775.1 PT|np_497749.1 st|K90752.1 st|K92038.1 PT|np_490856.1 PT|np_497284.1 st|K90752.1 st|K88095.1 PT|np_494764.1 ==> File 2 <==... (2 Replies)
Discussion started by: sammy777888
2 Replies

2. UNIX for Dummies Questions & Answers

Merging two text files by a column and filling in the missing values

Hi, I have to text files that I want to merge by the first column. The values in the first column pretty much match for the first part. However there are some values that are present in column 1 and not present in column 2 or vice versa. For such values I would like to substitute X for the... (9 Replies)
Discussion started by: evelibertine
9 Replies

3. Shell Programming and Scripting

How to use sort with null values?

Hello everyone I am doing a join command. Obviously, before I need two files sorted first. ( Both files have headers and have about 2 million lines each one ) The problem is, one of the files has null values in the key to sort (which is the first filed ). For example I have the original... (4 Replies)
Discussion started by: viktor1985
4 Replies

4. Programming

Filling the class from values taken from user input

I have a program that accepts user input. For example I have mdacc that the user sets the value. I then have a class which stores the value set by the user. I use set_param to set the values in the class. I pass through it the list of user defines arguments from argv. What would be the opinion on... (0 Replies)
Discussion started by: kristinu
0 Replies

5. UNIX for Dummies Questions & Answers

Cut from tables based on column values

Hello, I have a tab-delimited table that may contain 11,12 or 13 columns. Depending on the number of columns, I want to cut and get a sub table as shown below. However, the awk commands in the code seem to be an issue. What should I be doing differently? #cut columns 1-2,4-5,11 when 12 &... (3 Replies)
Discussion started by: Gussifinknottle
3 Replies

6. Shell Programming and Scripting

mapping of values in shell scripting

sample content of file1: SSTY1 2145228348 652011011715140100000002419005432092074 008801726143662 VDZX01 MIO2 008801726143662 SSRTY 2145228349 ... (3 Replies)
Discussion started by: vsachan
3 Replies

7. Shell Programming and Scripting

sorting null values

Hi I have a file with the values abc res set kls lmn ops i want to sort this file with the null values at the bottom of the file OUTPUT should look like this abc kls lmn ops (6 Replies)
Discussion started by: vickyhere
6 Replies

8. UNIX for Advanced & Expert Users

How to Compare Null values??

Hi, Can someone help me comparing Null values. Scenario is as follows: I have a variable which "cache_prd" which can have either some integer or nothing(Null) if it is integer I have to again do some comparision but these comparisons give me this error:( "line 32: [: 95: unary operator... (3 Replies)
Discussion started by: Yagami
3 Replies

9. Shell Programming and Scripting

[BASH] mapping of values from file line into variables

Hello, I've been struggling with this for some time but can't find a way to do it and I haven't found any other similar thread. I'd like to get the 'fields' in a line from a file into variables in just one command. The file contains data with the next structure:... (4 Replies)
Discussion started by: semaler
4 Replies

10. Programming

Xlib - Mapping Pixel Values to RGB Colors

Hi everyone... I'm working with XLib and I want to find out the pixel value of a particular point on screen and then map it to RGB values. I used XGetImage and XGetPixel to get the pixel value, but how do I get the RGB values of the pixel? I went through a few XLib manuals, there seems to be no... (1 Reply)
Discussion started by: thebin
1 Replies
Login or Register to Ask a Question