I may be missing something but I don't know how you find out which attribute is "Credit rating". I don't see any solution outside the two queries, because you have in the data_map table metadata, rather than data. However, a 'case' might do a lot of good to minimize operations when generating the query to actually execute.
If you ever find the "designers", shoot 'em. Stephane Faroult RoughSea Ltd <http://www.roughsea.com> Konagora <http://www.konagora.com> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd> On 04/01/2011 11:00 PM, Michael Moore wrote:
ok, I'll give it a try and let you know how it went. :-)On Fri, Apr 1, 2011 at 1:55 PM, Iotzov, Iordan <IIotzov@xxxxxxxxxxxxxxx <mailto:IIotzov@xxxxxxxxxxxxxxx>> wrote: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> [mailto: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 <mailto: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 1. select data_map_fk from ABC 2. select attribute_info from DATA_MAP using data_map_fk 3. 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.
