Re: PL/SQL question: concatenate fields to make unique key

From: Martin Douglas <Martin.Douglas_at_Boeing.com>
Date: Thu, 19 Aug 1999 15:21:51 GMT
Message-ID: <37BC210F.59E10D34_at_Boeing.com>


You could use a view... but if you ever need to do some joining with this on the fly generated key, your system will crawl. Why not add a column, populate it with the concatenated key just once, and then tweak the procedures via overloading to use the new key. Of course, the old way will still work since you're simply overloading.

Best regards...

Martin Douglas

saustin_deja_at_my-deja.com wrote:
>
> I'm working with a database in which the unique key is actually
> a concatenation of fields within each table. Each table that
> needs to be joined has all the individual fields, but to join
> them together requires that the concatenations match.
>
> For example the unique key could be
> ip_address||':'||port_number
> assuming each table has fields ip_address and port_number.
>
> I'd truly love to encapsulate the concatenation as a
> "virtual field" called instance_id, i.e. so instead of
>
> -- find all the a_channels that are also b_channels
> SELECT * FROM a_channels , b_channels
> WHERE a_channels.ip_address = b_channels.ip_address AND
> a_channels.port_number = b_channels.port_number
>
> I think I can do
>
> SELECT ip_address||':'||port_number instance_id, *
> FROM a_channels, b_channels
> WHERE a_channels.instance_id = b_channels.instance_id
>
> but I don't want to have to code in that SELECT clause all
> over the place (in case the definition of instance_id
> has to change). What I need is for instance_id to be
> like a new object type so I could do things like
>
> -- scan the list of all reported data and identify the set
> -- of b_channels that reported in this interval, assuming
> -- each b_channel could report several times
> SELECT DISTINCT instance_id FROM b_chan_reported_data ;
>
> etc.
>
> I thought about defining a set of overloaded functions to
> implement instance_id, in one case it would take a table
> name as a parameter and return a cursor, in another it would
> take a cursor and return a row, etc. but this seemed like a
> lot of work using DBMS_SQL etc. and still wouldn't cover all
> the cases I'd have to handle.
>
> Am I missing something obvious? It would seem this is a
> really common thing to do. BTW using Oracle 8i & PL/SQL.
>
> Thanks
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Aug 19 1999 - 17:21:51 CEST

Original text of this message