I have a source table that I want to migrate to a new system while performing a dictionary-based data transformation (its about translate the cateogry and subcategory name based on the dict table).
Ideally this could be done with a pl/sql script
The table looks like this:
| Subcategory | ID_Category | ID_Category |
|---|---|---|
| Thriller | 1 | 1 |
| Sci-Fi | 2 | 1 |
| Fantasy | 3 | 1 |
| Klasyka | 4 | 1 |
| Telefon | 1 | 2 |
| Komputer | 2 | 2 |
| Zegarek | 3 | 2 |
| Rock | 1 | 3 |
| Pop | 2 | 3 |
| Klasyka | 3 | 3 |
| Rap | 4 | 3 |
| Lego | 1 | 4 |
| Pokemon | 2 | 4 |
| Komedia | 1 | 5 |
| Dramat | 2 | 5 |
| Fantasy | 3 | 5 |
| News | 1 | 6 |
| Audiobook | 2 | 6 |
Where category table looks like that:
| Kategoria | ID_Kategoria |
|————-|—————|
| Książki | 1 |
| Elektronika | 2 |
| Muzyka | 3 |
| Zabawki | 4 |
| Filmy | 5 |
| Media | 6 |
The dictionary table is as follows:
| Source | Target |
|---|---|
| Książki | Books |
| Elektronika | Elektronics |
| Muzyka | Music |
| Zabawki | Toys |
| Filmy | Movies |
| Thriller | Thriller |
| Sci-Fi | Sci-Fi Fantasy |
| Fantasy | Sci-Fi Fantasy |
| Klasyka | Classic |
| Telefon | Phone |
| Komputer | Computer |
| Zegarek | Watch |
| Rock | Rock |
| Pop | Pop |
| Klasyka | Classics |
| Rap | Rap |
| Lego | Lego |
| Pokemon | Pokemon |
| Komedia | Comedy |
| Dramat | Drama |
| Fantasy | Fantasy |
| Media | Media |
| News | News |
| Audiobook | Audiobook |
| Media | Media |
My goal is to migrate the data in such a way that for the Books category the Sci – Fi and Fanstasy subcategory is changed to Sci-Fi Fantasy (as in the dictionary).
However, for the other categories in which fantasy appears the migration should take place without changes.
The transformation with migration for the Categories table was quite easy for me because I didn’t have a value there with the same key and a different value for the same key, but I can’t write a properly working script to migrate the subcategories because I have two items in the dictionary where the key appears
Source = Fantasy for Target = Sci – Fi Fantasy and for the key Target = Fantasy and I don’t know how to combine it to make it work
The result of subcategory table migration should look like this:
| Subcategory | Subcategory_ID | Category_ID |
|---|---|---|
| Thriller | 1001 | 1 |
| Sci-Fi Fantasy | 1002 | 1 |
| *to be combined with Sci-Fi | ||
| Classic | 1003 | 1 |
| Phone | 2001 | 2 |
| Computer | 2002 | 2 |
| Watch | 2003 | 2 |
| Rock | 3001 | 3 |
| Pop | 3002 | 3 |
| Classics | 3003 | 3 |
| Rap | 3004 | 3 |
| Lego | 4001 | 4 |
| Pokemon | 4002 | 4 |
| Comedy | 5001 | 5 |
| Drama | 5002 | 5 |
| Fantasy | 5003 | 5 |
| *to be added | *to be added | *to be added |
| *to be added | *to be added | *to be added |
Karol Witkowski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.