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

[Quoted] Yes, you are missing something obvious. What you are describing is exactly the type of things that views are good for.

Ken

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.
>