Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Linking varrays in 2 separate tables
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)
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 Received on Fri Feb 24 2006 - 08:35:58 CST