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 -> Linking varrays in 2 separate tables

Linking varrays in 2 separate tables

From: JabbaJohn <aj_at_tma.co.uk>
Date: 24 Feb 2006 06:35:58 -0800
Message-ID: <1140791758.354215.26360@v46g2000cwv.googlegroups.com>


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 Received on Fri Feb 24 2006 - 08:35:58 CST

Original text of this message

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