RE: What is the best way to do this?

From: Iotzov, Iordan <IIotzov_at_newsamerica.com>
Date: Fri, 1 Apr 2011 16:55:46 -0400
Message-ID: <9287D00721407A4CBDC9925C789DBB6701A428DAA4_at_nam-wil-exc-l03.newsamerica.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Moore Sent: Friday, April 01, 2011 2:55 PM
To: oracle-l_at_freelists.org
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
3. Read entire ABC into an associative array
4. select attribute_info (column_name) from DATA_MAP using data_map_fk
5. 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.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 01 2011 - 15:55:46 CDT