Re: SQL Problem

From: Jurij Modic <jmodic_at_src.si>
Date: Sat, 18 Sep 1999 20:13:15 GMT
Message-ID: <37e8f240.6840477@news.arnes.si>

On Sat, 18 Sep 1999 14:17:44 +0200, Enrique García Illera <egi_at_tid.es> wrote:

>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`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.

Maybe the following construct looks a little clumsy, but I think it should work:

SELECT w.weight, t.col1, t.col2, t.col3   FROM tab t, weights w
  WHERE t.col1 = w.col1 AND t.col2 = w.col2 -- both columns match

UNION ALL                 -- only the first column matches

SELECT w.weight, t.col1, t.col2, t.col3   FROM tab t, weights w
  WHERE t.col1 = w.col1 AND w.col2 = '*'   AND NOT EXISTS (SELECT 1 FROM weights

                    WHERE col1 = t.col1 AND col2 = t.col2)
UNION ALL                 -- only the second column matches

SELECT w.weight, t.col1, t.col2, t.col3   FROM tab t, weights w
  WHERE w.col1 = '*' AND t.col2 = w.col2   AND NOT EXISTS (SELECT 1 FROM weights

                    WHERE col1 = t.col1 AND col2 = t.col2)
UNION ALL                 -- none of the columns matches

SELECT w.weight, t.col1, t.col2, t.col3   FROM tab t, weights w
  WHERE w.col1 = '*' AND w.col2 = '*'
  AND NOT EXISTS (SELECT 1 FROM weights

                    WHERE col1 = t.col1 AND col2 = t.col2
                    OR col1 = t.col1 AND col2 = '*'
                    OR col1 = '*' AND col2 = t.col2);

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Sat Sep 18 1999 - 15:13:15 CDT