Re: What is the best way to do this?
Date: Wed, 13 Apr 2011 03:45:32 -0600
Message-ID: <BANLkTik0M9g=zx=NvAJECpOADHN9S_DqeA_at_mail.gmail.com>
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 INDEX10 );
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?
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 13 2011 - 04:45:32 CDT