Re: What is the best way to do this?
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: rmhammond723@xxxxxxxxx
- Date: Thu, 14 Apr 2011 10:36:42 -0700
Hi Reed,
Very interesting approach! Kinda looks like a pivot. I don't know how it
will scale with 210 attributes though. I'll have to try it.
Thanks for the response.
Regards,
Mike
On Wed, Apr 13, 2011 at 2:45 AM, Reed Hammond <rmhammond723@xxxxxxxxx>wrote:
> 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:
