Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Linking varrays in 2 separate tables
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