We have a single table that contains all the list of values (not master tables) for each language. This leads to a lot of self joins. Is there a better way to do handle this?
Example:
CREATE TABLE SYSTEM_CONSTANTS
(
SYS_CODE VARCHAR2(10),
SYS_DESC VARCHAR2(50),
SYS_LANG VARCHAR2(10),
SYS_DISP_ENG VARCHAR2(100),
SYS_DISP_LANG VARCHAR2(100),
SYS_VALUE NUMBER,
PRIMARY KEY(SYS_CODE, SYS_LANG, SYS_VALUE)
) ;
INSERT INTO SYSTEM_CONSTANTS(SYS_CODE, SYS_DESC, SYS_LANG, SYS_DISP_ENG, SYS_DISP_LANG, SYS_VALUE) VALUES
( 'GENDER', 'Gender of the person', 'jp', 'Male', 'Male in Japanese character', 0) ;
INSERT INTO SYSTEM_CONSTANTS(SYS_CODE, SYS_DESC, SYS_LANG, SYS_DISP_ENG, SYS_DISP_LANG, SYS_VALUE) VALUES
( 'GENDER', 'Gender of the person', 'jp', 'Female', 'Female in Japanese character', 1) ;
INSERT INTO SYSTEM_CONSTANTS(SYS_CODE, SYS_DESC, SYS_LANG, SYS_DISP_ENG, SYS_DISP_LANG, SYS_VALUE) VALUES
( 'YN', 'Yes or No Indicator', 'jp', 'Yes', 'Yes in Japanese', 0) ;
INSERT INTO SYSTEM_CONSTANTS(SYS_CODE, SYS_DESC, SYS_LANG, SYS_DISP_ENG, SYS_DISP_LANG, SYS_VALUE) VALUES
( 'YN', 'Yes or No Indicator', 'jp', 'No', 'No in Japanese', 1) ;
INSERT INTO SYSTEM_CONSTANTS(SYS_CODE, SYS_DESC, SYS_LANG, SYS_DISP_ENG, SYS_DISP_LANG, SYS_VALUE) VALUES
( 'TRG_TYPE', 'Training Type', 'jp', 'Oracle Training', 'Oracle Training in Japanese', 1) ;
INSERT INTO SYSTEM_CONSTANTS(SYS_CODE, SYS_DESC, SYS_LANG, SYS_DISP_ENG, SYS_DISP_LANG, SYS_VALUE) VALUES
( 'TRG_TYPE', 'Training Type', 'jp', 'Microsoft Training', 'Microsoft Training in Japanese', 2) ;
-- ..................... more inserts ....... --
COMMIT ;
The idea is whenever we use a list of value we select from the system constants with the relevant sys_code like:
SELECT DECODE(:P10_LANGUAGE, NULL, SYS_DISP_ENG, SYS_DISP_LANG), SYS_VALUE
FROM SYSTEM_CONSTANTS
WHERE SYS_CODE = :P10_CODE
AND SYS_LANG = NVL(:P10_LANGUAGE, SYS_LANG) ;
The question is we usually have search screens where the data to be displayed is the SYS_DISP_LANG. So, the more list of values data we have in the search results, the more self joins we need. Does it make any difference if we have each of the LOV in a table by itself or to have one table which has all the list of values (select lists)?
CREATE TABLE my_emp
( EMP_ID NUMBER,
ENAME VARCHAR2(50),
GENDER NUMBER,
TRAINING_ID NUMBER,
SELECTED_IND NUMBER,
TRG_IND NUMBER,
PRIMARY KEY(EMP_ID)
) ;
-- Value of Gender, training_id, selected_ind, trg_ind is from the client as select lists from the system_constants table --
-- For completeness inserting the values directly --
INSERT INTO my_emp VALUES (100, 'AAA', 1, 2, 1, 0) ;
INSERT INTO my_emp VALUES (101, 'BBB', 0, 1, 0, 1) ;
INSERT INTO my_emp VALUES (102, 'CCC', 1, 1, 1, 0) ;
INSERT INTO my_emp VALUES (103, 'DDD', 1, 1, 1, 0) ;
INSERT INTO my_emp VALUES (104, 'EEE', 0, 2, 0, 1) ;
INSERT INTO my_emp VALUES (105, 'FFF', 0, 2, 1, 0) ;
COMMIT ;
SELECT ENAME,
DECODE(:P10_LANGUAGE, NULL, gender.SYS_DISP_ENG, gender.SYS_DISP_LANG) gender,
DECODE(:P10_LANGUAGE, NULL, trg.SYS_DISP_ENG, trg.SYS_DISP_LANG) training_type,
DECODE(:P10_LANGUAGE, NULL, sel.SYS_DISP_ENG, sel.SYS_DISP_LANG) selected_flag,
DECODE(:P10_LANGUAGE, NULL, trg_comp.SYS_DISP_ENG, trg_comp.SYS_DISP_LANG) training_completed_flag
FROM my_emp,
system_constants gender, system_constants trg, system_constants sel,
system_constants trg_comp
WHERE my_emp.gender = gender.sys_value
AND gender.sys_code = 'GENDER'
AND gender.sys_lang = :P10_LANGUAGE
--
AND my_emp.training_id = trg.sys_value
AND trg.sys_code = 'TRG_TYPE'
AND trg.sys_lang = :P10_LANGUAGE
--
AND my_emp.selected_ind = sel.sys_value
AND sel.sys_code = 'YN'
AND sel.sys_lang = :P10_LANGUAGE
--
AND my_emp.trg_ind = trg_comp.sys_value
AND trg_comp.sys_code = 'YN'
AND trg_comp.sys_lang = :P10_LANGUAGE ;
This becomes more complicated if one of the values like training_id or selected_ind is NULL. Then the code becomes even more complicated with inner queries and outer joins. Because of this complication, the developers have created a function called get_description(vc_code, vc_value, vc_language) that returns the description in the desired language and using it everywhere. So, the above query becomes,
SELECT ENAME, get_description('GENDER', gender, :P10_LANGUAGE) gender,
get_description('TRG_TYPE', training_id, :P10_LANGUAGE) training_type,
get_description('YN', selected_ind, :P10_LANGUAGE) selected_flag,
get_description('YN', trg_ind, :P10_LANGUAGE) training_completed_flag
FROM my_emp ;
This query leads to context switching.
What is the better way of doing this?
DO NOT DO NOT DO NOT do the last one.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48704116042682#1377078900346297424 In fact, do NOT do what you've already done. You should use a lookup table per type of lookup, not a 'generic' lookup for everything.
Use outer joins, use a view. And use a separate table for EACH lookup.
So,
a) use a separate table for each lookup
b) hide the "complexity" if you need in a view
c) DO NOT use the plsql 'getter' functions as you have
There are two approaches to your view, one would be:
create view..
select ename,
(select descript from gender_lookup where code = EMP.gender) gender,
(select descript from training_type_lookup where code = EMP.tt_code) tt,
....
from EMPor
create view ..
select emp.ename, gl.descript gender, tt.descript training_type, ....
from EMP, gender_lookup gl, training_type_lookup tt, ...
where <outer joins>
the first one works nicely if you typically get a small number of rows from EMP in your application at a time, the second works nicely if you tend to get many/most/all of the rows from emp - so you might use BOTH views in different places.