RE: What is the best way to do this?
- From: "Iotzov, Iordan" <IIotzov@xxxxxxxxxxxxxxx>
- To: "michaeljmoore@xxxxxxxxx" <michaeljmoore@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 1 Apr 2011 16:55:46 -0400
Hi Mike,
An Oracle CASE construct might be what you need.
The code could look something like this:
select customer_num , (case
when data_map.attribute_info = 'attr001' then abc. attr001
when data_map.attribute_info = 'attr002' then abc. attr002
...
...
)
from abc , data_map
where abc. data_map_fk = data_map. data_map_????
Regards,
Jordan
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Michael Moore
Sent: Friday, April 01, 2011 2:55 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: What is the best way to do this?
I have a table with some specifically-named columns and a group of 210 generic
column. For example:
table abc
customer_num
data_map_fk
attr001
attr002
attr003
attr004
So, in the example above, data_map_fk points to another table (say DATA_MAP)
which, in essence, tells you the meaning of attr001 thru attr004 for the given
customer.
In this way, Customer A's credit rating might be in attr002, while customer B's
credit rating might be in attr004.
Unfortunately, in reality, table ABC has 210 generic ATTRnnn columns each
VARCHAR2(4000); ABC represents our core business transaction and we process
100's of 1000's of them. Most of the time, as we batch process these
transactions, we are only interested in 4-5 of the 210 ATTRnnn columns but we
don't know which 4-5 until we look up the DATA_MAP.
Now at this point you may be tempted to critique this design however this is
the GIVEN I have to work with.
I have tried the following two approaches to obtaining the values from the
target attributes:
1. The dynamic SQL approach
* select data_map_fk from ABC
* select attribute_info from DATA_MAP using data_map_fk
* EXECUTE IMMEDIATE (select || targ_attr_name|| from ABC
2. associative array
examp:
FETCH cur_ABC
INTO data_map_fk,
m ('ATTRIBUTE01'),
m ('ATTRIBUTE02'),
m ('ATTRIBUTE03'),
m ('ATTRIBUTE04'),
m ('ATTRIBUTE05'),
m ('ATTRIBUTE06'),
m ('ATTRIBUTE07'),
m ('ATTRIBUTE08'),
...
m ('ATTRIBUTE210')
where abc_key = customer_num; -- customer_num is primary key
1. Read entire ABC into an associative array
2. select attribute_info (column_name) from DATA_MAP using data_map_fk
3. target_data_value := m(column_name)
Solution 1 has the disadvantage of needing to SELECT on ABC two times and needs
to use DYNAMIC SQL
Solution 2 seems to be a little slow, maybe due to loading the array or the
transfer of large amounts of data.
So, questions:
1) is there a better approach which I have not considered?
2) is there a way to make Solution 2 run faster?
3) any other thoughts?
Regards,
Mike
________________________________
This message and its attachments may contain legally privileged or confidential
information. It is intended solely for the named addressee. If you are not the
addressee indicated in this message (or responsible for delivery of the message
to the addressee), you may not copy or deliver this message or its attachments
to anyone. Rather, you should permanently delete this message and its
attachments and kindly notify the sender by reply e-mail. Any content of this
message and its attachments that does not relate to the official business of
News America Incorporated or its subsidiaries must be taken not to have been
sent or endorsed by any of them. No warranty is made that the e-mail or
attachment(s) are free from computer virus or other defect.
Other related posts:
