Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Linking varrays in 2 separate tables

Re: Linking varrays in 2 separate tables

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 24 Feb 2006 09:15:01 -0800
Message-ID: <1140801284.200390@jetspin.drizzle.com>


JabbaJohn wrote:
> I have 2 tables in Oracle 9i which contain a varray in each. Each
> varray has 50 elements (which doesn't change). One of the varrays is a
> set of values in a table called VT and the other is in table FT and is
> a set of factors. For each row in VT, 50 values are multiplied by 1 or
> more related rows of 50 factors in FT (linked by COL_A and COL_B in the
> example). I need to return a row for each value/factor pair where VT
> links to FT. I hope that's explained it OK...
>
> I have a solution which works but it seems a little messy. Does anyone
> have some neater way of linking the varrays?
>
> SELECT col_a, col_b, val_index, val, factor
> FROM (SELECT inner1.col_a,
> inner1.col_b,
> inner1.val,
> inner1.val_index,
> CASE WHEN MOD(rownum, 50) = 0 THEN 50 ELSE MOD(rownum,
> 50) END factor_index,
> f.column_value factor
> FROM (SELECT vt.col_a,
> vt.col_b,
> factors,
> CASE WHEN MOD(rownum, 50) = 0 THEN 50 ELSE
> MOD(rownum, 50) END val_index,
> v.column_value val
> FROM values_table vt, factors_table ft,
> TABLE(vt.vals) v
> WHERE vt.col_a = ft.col_a
> AND vt.col_b = ft.col_b) inner1,
> TABLE(inner1.factors) f)
> WHERE factor_index = val_index;
>
> This is actually a much simplified version of the schema and SQL I'm
> actually using but I hope this gives everyone enough to go on...
>
> Thanks

My solution would be to take Tom Kyte's advice about using non-relational tables and redesign the storage.

If you want object-relational access build it into a view.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Fri Feb 24 2006 - 11:15:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US