Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-04-10
5
Medium Priority
?
301 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

916 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