Re: What is the best way to do this?
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: "Iotzov, Iordan" <IIotzov@xxxxxxxxxxxxxxx>
- Date: Fri, 1 Apr 2011 14:00:45 -0700
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>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] *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
> 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.
>
Other related posts:
