PL/SQL question: concatenate fields to make unique key
Date: Wed, 18 Aug 1999 20:27:37 GMT
Message-ID: <7pf4v9$h26$1_at_nnrp1.deja.com>
[Quoted] [Quoted] 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 Wed Aug 18 1999 - 22:27:37 CEST