Re: What is the best way to do this?
Date: Fri, 01 Apr 2011 23:37:06 +0200
Message-ID: <4D964582.1030900_at_roughsea.com>
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_at_newsamerica.com <mailto:IIotzov_at_newsamerica.com>> 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_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto: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 <mailto: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
> 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.
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 01 2011 - 16:37:06 CDT