I've known this trick since I started to use relational database. The trick is about how to convert a row of record, such as column1 column2 column3 ... columnN, to
column1
column2
column3
...
columnN
Until recently I found the perfect place where I can use it in application.
In the application, we store certain account types as a parameter stored in parameter table. Such as : select value from parameter_table where parameter_nm='ACCT_PLAN_TYPE', you'll get RRSP,401K,RESP,LIF. The commonly used logic for this information is these account types will comprise of an in-list as a condition of the query. Imagine how developer will use this information. They will write query to query the parameter table, then parse the string, eliminate the "," and space, store the values into an array, or sort of collection. Then test the query result against the collection. Or they can use dynamic sql, just append the list as an in-list to the query. Neither way seems nice. It is my belief that database should not only be the place to store data, it should also be a development platform, i.e, it should facilitate programming, i.e, it should make programming easier. The idea is this type information should be stored in a separate table, then all the logic can be represented in standard join or in subquery, such as:
acct_type in (select acct_type from acct_plan_type)
Instead of arguing with other parties to move the information into separate table, I simply created a view using the previously mentioned horizontal to vertical trick:
CREATE OR REPLACE VIEW acct_plan_type_vw (
acct_plan_type )
AS
SELECT ltrim(rtrim(acct_plan_type)) acct_plan_type
FROM (SELECT (CASE
WHEN rn = 1 THEN
SUBSTR (acct_plan_type,1,
case
when INSTR (acct_plan_type, ',', 1, 1) >0 then
INSTR (acct_plan_type, ',', 1, 1)-1
else
length(acct_plan_type)
end
)
WHEN INSTR (acct_plan_type, ',', 1, rn - 1) > 0 THEN
SUBSTR (acct_plan_type, INSTR (acct_plan_type, ',', 1, rn - 1) + 1,
CASE
WHEN INSTR (acct_plan_type, ',', 1, rn) > 0 THEN
INSTR (acct_plan_type, ',', 1, rn) - INSTR (acct_plan_type,',',1,rn - 1)- 1
ELSE
LENGTH (acct_plan_type) - INSTR (acct_plan_type, ',', 1, rn - 1)
END
)
ELSE
NULL
END
) acct_plan_type
FROM (
SELECT parameter_val acct_plan_type FROM parameter_table
WHERE parameter_cd = 'ACCT_PLAN_TYPES'
) acct_plan_types,
(SELECT ROWNUM rn FROM parameter_table --only because parameter_table has more than 100 rows
WHERE ROWNUM < 100) aux)
WHERE acct_plan_type IS NOT NULL
/
Wednesday, August 8, 2007
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment