SQL Problem

Hi all. I don´t know if this is the appropiate place for this post because this is only an SQL problem and not an Oracle one, but I couldn´t find a SQL group.

I have a big table with N columns (col1, col2, col3 ... for example) and lot of rows and I want to assign a weight to every one. The value of the weight depends only of the fields col1 and col2, so I`ve another table called WEIGTHS (very small) with this structure:

weight      col1      col2
-------   ------     ------
   W1        v1         v2
   W2        v3         v4
   W3        v5          *
........... ..........      .......
   Wd        *            *

where '*' means 'every value for this column'. It`s a wildcard. This table says that if I`ve a row with values v1 and v2 for the columns col1 and col2, the weigth associated is W1 and so on. The row with the two '*' is obligatory, because all of the rows must have a weigth, and this is the "default" weight.

I want to create a view, combination of these two tables and with the same number of rows of the big table, that allow me to see an unique weight for each row. The structure of the view should be:

 weigth        col1        col2        col3   ....       colN
--------       ----        ----         ----              -----
    W1           v1          v2           xx                  yy
    W2           v3          v4           aa                  bb
  ..........        .......      ........       ........
..........
    Wd           aaa        bbb         ccc                cccc
    Wd           xxx        yyy          zzz                aaaa
    Wd          ddd        bbb         ccc                aaaa

I need the view because the weigths are going to be changed often and I don´t want to update the big table every time this happens.

I`ve tested several options, but I always obtained repeated rows. The ideal for me is to avoid every kind of repetition but, because of the nature of the applicaton, I can admit the repetition of rows that verifies two or more restrictive conditions (for example that col1='A' and that col1='A' and col2='B') but I can´t allow one row with the default weight and another row with a more restrictive one.

Many thanks in advance.

Received on Sat Sep 18 1999 - 07:17:44 CDT