Re: SQL Problem

From: <michael_bialik_at_my-deja.com>
Date: Sun, 19 Sep 1999 14:03:39 GMT
Message-ID: <7s2qfn$ntr$1@nnrp1.deja.com>

Hi.

 Try using PL/SQL function :

  CREATE PACKAGE get IS
   FUNCTION row_weight ( cl1 col1%TYPE, cl2 col2%TYPE )     RETURN wght weight%TYPE;
  END get;

  CREATE PACKAGE BODY get is
   FUNCTION row_weight ( cl1 col1%TYPE, cl2 col2%TYPE )     RETURN weight%TYPE IS
   wght weight%TYPE := NULL;
   BEGIN

     SELECT weight INTO wght FROM weight_tab
      WHERE col1 = cl1 AND col2 = cl2;
     RETURN wght;
   EXCEPTION
     WHEN NOT_FOUND THEN
     BEGIN
       SELECT weight INTO wght FROM weight_tab
        WHERE col1 = cl1 AND col2 = '*';
      RETURN whght;
     EXCEPTION
       WHEN NOT_FOUND THEN
       BEGIN
         SELECT weight INTO wght FROM weight_tab
          WHERE col1 = '*' AND col2 = '*';
        RETURN whght;
     END;

   END row_weight;
  end get;

CREATE VIEW vw_weight IS
 SELECT tab.*, get.row_weight ( tab.col1, tab.col2 )   FROM your_table tab;

P.S. You will need to create an index on weight table

     on COL1 and COL2 columns in order to get
     a decent performance.

Good luck. Michael.

In article <37E382E8.A844CA39_at_tid.es>,
  Enrique =?iso-8859-1?Q?Garc=EDa?= Illera <egi_at_tid.es> wrote:
> This is a multi-part message in MIME format.
> --------------C68410CC5C0683DD5C6C4750
> Content-Type: text/plain; charset=iso-8859-1
> Content-Transfer-Encoding: 8bit
>
> 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.
>
> --------------C68410CC5C0683DD5C6C4750
> Content-Type: text/x-vcard; charset=us-ascii;
> name="egi.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Enrique García Illera
> Content-Disposition: attachment;
> filename="egi.vcf"
>
> begin:vcard
> n:García Illera;Enrique
> x-mozilla-html:FALSE
> org:Telefónica I+D
> version:2.1
> email;internet:egi_at_tid.es
> title:EOC-Conmutación
> x-mozilla-cpt:;0
> tel;work:913379868
> fn:Enrique García Illera
> end:vcard
>
> --------------C68410CC5C0683DD5C6C4750--
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sun Sep 19 1999 - 09:03:39 CDT