?
Solved

How to replace id-value with new fictive id in oracle

Posted on 2012-04-10
5
Medium Priority
?
302 Views
Last Modified: 2012-04-15
Hi!

I have a table with, let´s say, 4 columns, person, colA, col B and colC.

The first column contains personal number (ssn) so when I export data to outside persons I dont want to send also the actual value in column.  Instead I want to replace, in this case "760320" with "1" and 760914 with "2".  Please note that 760320 and other personal numbers can occure 1, 2 or several times.


person      col A          col B            col C
760320      x          y                       z
760320      x          y                       z
760914      x          y                       u

The result should be

person  col A colB       col C
1             x      y           z
1             x      y           z
2             x      y            u


Thanks in advance
0
Comment
Question by:marcgu
  • 2
  • 2
5 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1200 total points
ID: 37829712
select dense_rank() over(order by person) person, cola,colb,colc from yourtable
order by person
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37829717
>>when I export data to outside

Do you mean Oracle Export?

If you are running 11g and plan on using datapump, check out:  
Data Pump Data Remapping (Obfuscation)

http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/DPUMP4/Default.aspx

If you mean some other type of expert, please clarify.
0
 

Author Comment

by:marcgu
ID: 37829759
Hi!

Thanks for really fast possible solutions.

sdstuber: I will try your solution at work tomorrow.

slightwv: I wrote export, but the only import thing is really to get the result from the select-query in SQL developer . From there I can choose to copy the rows from the select or right click  to export. Sorry if this caused false focus.
/Marcus
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37829770
Are you only wanting a single table or will you be exporting additional related tables and need to keep the obfuscated values equal across the exports?
0
 

Author Closing Comment

by:marcgu
ID: 37848223
Thanks for perfect solution.
/Marcus
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question