Oracle APEX - Lookup Table with several links

Question

I have a question related to the lookup table creation.

Assume this scenario: I have several tables related to the World Populations with different info, such as TOTAL, FEMALE, MALE, OVER_65, UNDER_15. These are all different tables. Every table has the country name as a column. I can create a lookup table from the country name based on 1 table, like TOTAL.

How can I also implement same action to other tables to map the new lookup table? I can't recreate, because the object is already there. The data is same. I want to update the country name column with the country id in other tables based on the created look up table.

I am also searching for the answer. If i find during this time, i will post it.

[EDIT] I have also created APEX screenshots that explains my question.

Answer 1

When you create a lookup table in the SQL Workshop, APEX generates several DDL and DML statements to do the job. In the last step of the wizard, you should be able to expand the SQL region at the bottom to see the code. Unfortunately, it doesn't come out well-formatted, but it's not too hard to clean up.

As a test, I went in and created a lookup table on the JOB column of the EMP table. Here's the code that was generated. I've formatted it and added comments to explain the parts you'll need and the ones you will not.

/*
* Creates the lookup table. Not needed after the first pass.
*/
create table "JOB_LOOKUP"(
  "JOB_ID" number not null primary key, 
  "JOB" varchar2(4000) not null
);

/*
* Creates the sequence for the primary key of the lookup table. 
* Not needed after the first pass.
*/
create sequence "JOB_LOOKUP_SEQ";

/*
* Creates the trigger that links the sequence to the table.
* Not needed after the first pass. 
*/
create or replace trigger "T_JOB_LOOKUP" 
before insert or update on "JOB_LOOKUP" 
for each row 
begin 
if inserting and :new."JOB_ID" is null then 
  for c1 in (select "JOB_LOOKUP_SEQ".nextval nv from dual) loop 
    :new."JOB_ID" := c1.nv;   end loop; end if; 
end;
/

/*
* Inserts the distinct values from the source table into the lookup
* table. If the lookup table already contains ALL of the needed values,
* country names in your case, then you can skip this step. However, if
* the source table has some values that are not in the lookup table, then
* you'll need to execute a modified version of this step. See notes below.
*/
insert into "JOB_LOOKUP" ( "JOB" ) 
select distinct "JOB" from "DMCGHANTEST"."EMP"
where "JOB" is not null;

/*
* The rest of the statements add the foreign key column, populate it,
* remove the old column, rename the new column, and add the foreign key.
* All of this is still needed.
*/
alter table "EMP" add "JOB2" number;

update "EMP" x set "JOB2" = (select "JOB_ID" from "JOB_LOOKUP" where "JOB" = x."JOB");

alter table "EMP" drop column "JOB";
alter table "EMP" rename column "JOB2"  to "JOB_ID";
alter table "EMP" add foreign key ("JOB_ID") references "JOB_LOOKUP" ("JOB_ID");

As for the insert statement that populates the lookup table, here's the modified version you'll need:

insert into "JOB_LOOKUP" ( "JOB" ) 
select distinct "JOB" from "DMCGHANTEST"."EMP"
where "JOB" is not null
  and "JOB" not in (
    select "JOB"
    from JOB_LOOKUP
  );

That will ensure only new, unique values are added to the lookup table.