Re: What is the best way to do this?
- From: Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 12 Apr 2011 08:35:24 +0200
I would second to what Yechiel wrote - assuming You are able to add an
own table and change the meaning of the value stored in ABC table ie.
instead of asking for student_age and getting attribute134 in return You
could get for student_age just an id of the attribute (assuming You get
one by one).
In this case it is even possible one asks for few attributes at once
with IN clause instead of equality.
And only few columns in table (though more rows), and no need for
dynamic sql
Regards
Remigiusz
W dniu 04/05/11 06:33, Yechiel Adar pisze:
> In this case I think I will normalize it by building a table:
> 1) Id
> 2) Attribute name
> 3) Attribute value
>
> and build a trigger on ABC that will update that table whenever ABC is
> updated.
> Then all you have to do is search on the new table for specific entry
>
> Since we are talking about a customer table is is probably not updated
> much so the trigger will not hurt you during normal operation.
>
> Yechiel Adar
> Israel
>
>
> On 04/04/2011 20:20, Michael Moore wrote:
>> Hi Yechiel,
>>
>> I can't really do that because I am limited to modification only
>> within my PLSQL FUNCTION. I can not change the program that calls my
>> function.
>>
>>
>> My task is to optimize/rewrite an _existing function_ which looks like:
>>
>> FUNCTION get_attribute_value (customer_number IN NUMBER,
>> virtual_column_name IN VARCHAR2)
>> returns varchar2;
>>
>> -- where the _return value_ is the value of _one_ of ATTR001 thru
>> ATTR201.
>>
>> My function does not "know" _all_ of the ATTRibute names, it only
>> know the ONE that it is currently processing.
>>
>> There might be, for example, 31, 37 or 121 attributes that have a
>> mapping defined, but maybe only 1 of those attributes is required for
>> pricing. For example:
>>
>> abc_key = 778899
>> customer # 12345
>> attribute07 = 3.5 -- student GPA
>> attribute121 = 95050 -- zip code
>> attribute129 = Agriculture -- program of interest
>> attribute134 = 25 -- age
>>
>> Now lets assume that for this customer we have the business rule that
>> says:
>> If the student's age > 23 charge the customer $21
>> If the student's age <= 23 charge the customer $29
>>
>> My function will be passed (778899,'student_age')
>> My function will SELECT customer# from ABC where abc_key = 778899;
>> My function will figure out that for customer # 12345, 'student_age'
>> is stored in attribute134.
>> My function will select attribute134 from ABC where abc_key = 778899;
>> My function will return '25'
>>
>> attribute07, 121 and 129 are completely irrelevant to my process as
>> they have no affect on the price.
>>
>> Thanks for your help,
>>
>> Regards,
>> Mike
>>
--
Pole nakazi
----------------------------------------------------------------------
Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
pos : DBA at DIiUSI
addr : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland
phone : +48 58 667 17 43
mobile: +48 602 42 42 77
Nordea Bank Polska S.A. z siedziba w Gdyni, ul. Kielecka 2, 81-303 Gdynia,
wpisana do Rejestru Przedsiebiorców Krajowego Rejestru Sadowego pod numerem:
0000021828,
dla której dokumentacje przechowuje Sad Rejonowy Gdansk - Pólnoc w Gdansku,
VIII Wydzial Gospodarczy Krajowego Rejestru Sadowego,
o kapitale zakladowym i wplaconym w wysokosci: 277.493.500,00 zlotych,
NIP: 586-000-78-20, REGON: 190024711--
//www.freelists.org/webpage/oracle-l
Other related posts:
