Re: Multivalue field joins

On Mar 29, 9:54 am, "Goog79" <rachellara1..._at_gmail.com> wrote:
> Hi everyone,
>
> I've tried searching and searching and have already searched this
> group to no avail. Can anyone assist in how to do a join using a
> multivalue field in Oracle please?
>
> For example, a field in an Order table has a multivalue field called
> Products, which might hold something like this:
>
> 123;234;345
>
> The codes correspond to the primary key in the Product table, and I
> want to get the ProductName from the Product table now for each code
> (123, 234 and 345). How would I do this??? Is this a completely
> crazy question??? I can't find an answer anywhere and am starting to
> feel stupid!!!
>
> Thanks so much in advance everyone!

Why one would 'design' a table in such a manner is unknown to me. You cannot directly compare single-valued fields to this multi-valued mess; you will need to extract the values from your multi-valued field individually then make your comparison, and you'll need to use the instr() and substr() functions within an anonymous PL/SQL block or in a stored procedure.

Such fields are not logical in a relational sense. This table needs to be redesigned.

David Fitzjarrell Received on Thu Mar 29 2007 - 11:00:37 CDT