Re: What is the best way to do this?
- From: Reed Hammond <rmhammond723@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 13 Apr 2011 03:45:32 -0600
Mike,
You may have already answered this but here is my take on the question.
I needed to set up some tables and made some assumptions on how your data is
set up. They may be wrong. here is my small sample set and a query based on
these tables:
XE> create table attr (
2 attr_id number
3 , attr_nm varchar2 (50 char)
4 ,constraint attr_pk PRIMARY KEY (attr_id) USING INDEX
5 );
Table created.
XE> create table data_map (
2 data_map_id number
3 , attr_fk number
4 , column_nm varchar2 (10 char)
5 ,constraint data_map_pk PRIMARY KEY (data_map_id,attr_fk) USING INDEX
6 ,constraint dm_attr_fk foreign key (attr_fk) references attr (attr_id)
enable
7 );
Table created.
XE> create table abc (
2 customer_num number
3 , data_map_fk number
4 , attr001 varchar2 (4000 char)
5 , attr002 varchar2 (4000 char)
6 , attr003 varchar2 (4000 char)
7 , attr004 varchar2 (4000 char)
8 , attr005 varchar2 (4000 char)
9 ,constraint abc_pk PRIMARY KEY (customer_num) USING INDEX
10 );
Table created.
XE> insert into attr values (1,'name');
1 row created.
XE> insert into attr values (2,'credit_rating');
1 row created.
XE> insert into attr values (3,'gpa');
1 row created.
XE> insert into attr values (4,'zip_code');
1 row created.
XE> insert into attr values (5,'agriculture');
1 row created.
XE> insert into attr values (6,'age');
1 row created.
XE> insert into attr values (7,'address');
1 row created.
XE> insert into attr values (8,'city');
1 row created.
XE> insert into attr values (9,'state');
1 row created.
XE> insert into abc values (1234,1,'Jon Doe','25','3.5','AnyTown','NV');
1 row created.
XE> insert into data_map values (1,1,'ATTR001');
1 row created.
XE> insert into data_map values (1,6,'ATTR002');
1 row created.
XE> insert into data_map values (1,3,'ATTR003');
1 row created.
XE> insert into data_map values (1,8,'ATTR004');
1 row created.
XE> insert into data_map values (1,9,'ATTR005');
1 row created.
XE> insert into abc values (1235,2,'19','1.8','Somewhere','NY','00123');
1 row created.
XE> insert into data_map values (2,6,'ATTR001');
1 row created.
XE> insert into data_map values (2,3,'ATTR002');
1 row created.
XE> insert into data_map values (2,8,'ATTR003');
1 row created.
XE> insert into data_map values (2,9,'ATTR004');
1 row created.
XE> insert into data_map values (2,4,'ATTR005');
1 row created.
XE> insert into abc values (1236,3,'210','Jane Doe','SomeAg','33','2.2');
1 row created.
XE> insert into data_map values (3,2,'ATTR001');
1 row created.
XE> insert into data_map values (3,1,'ATTR002');
1 row created.
XE> insert into data_map values (3,5,'ATTR003');
1 row created.
XE> insert into data_map values (3,6,'ATTR004');
1 row created.
XE> insert into data_map values (3,3,'ATTR005');
1 row created.
XE> commit;
Commit complete.
XE> variable cust_num number
XE> exec :cust_num := 1234;
PL/SQL procedure successfully completed.
XE> variable attr_nm varchar2(30)
XE> exec :attr_nm := 'age';
PL/SQL procedure successfully completed.
XE> column val format a20
XE> select val from
2 (select customer_num, data_map_fk
3 ,case lvl when 1 then 'ATTR001'
4 when 2 then 'ATTR002'
5 when 3 then 'ATTR003'
6 when 4 then 'ATTR004'
7 when 5 then 'ATTR005' end key
8 ,case lvl when 1 then attr001
9 when 2 then attr002
10 when 3 then attr003
11 when 4 then attr004
12 when 5 then attr005 end val
13 from (
14 select * from abc
15 inner join (select level lvl from dual connect by level<6) on 1=1
16 where customer_num = :cust_num)) cd
17 inner join data_map dm on dm.data_map_id = cd.data_map_fk and
dm.column_nm = cd.key
18 inner join attr on attr.attr_id = dm.attr_fk and attr.attr_nm =
:attr_nm;
VAL
--------------------
25
exec :cust_num := 1235;
PL/SQL procedure successfully completed.
XE> exec :attr_nm := 'city';
PL/SQL procedure successfully completed.
XE> /
VAL
--------------------
Somewhere
XE> exec :cust_num := 1236;
PL/SQL procedure successfully completed.
XE> exec :attr_nm := 'agriculture';
PL/SQL procedure successfully completed.
XE> /
VAL
--------------------
SomeAg
Is this close to what you are looking for?
Other related posts:
