Re: views...

From: James Lorenzen <james_lorenzen_at_allianzlife.com>
Date: Fri, 31 Dec 1999 16:51:40 GMT
Message-ID: <84im72$j8u$1@nnrp1.deja.com>

maylee, If I understand your request correctly, this can be a straight forward outer join like I presented earlier. If you are Oracle 8 (or 7.3), the following should work for you. Note, I am using inline views combined with an outer join on the inline view.

Assume the tables are:

 a (col1 number, col2 number, col3 number, col4 number, col5 number,
    col6 number, col7 number, col8 number, col9 number, col10 number)
 b (col1 number, col2 number, ...)

 c (col1 number)

The view should have eleven columns (col1, col2, col3, ..., col10, new_col). And new_col in the view should be:

    1 if col2 in b is found in col1 of c  and

    2 if col2 in b is not found in col1 of c (or is NULL)

CREATE VIEW v1 (col1, col2, col3, col4, col5, col6, col7, col8,

     col9, col10, new_col) AS
  SELECT a.col1, a.col2, a.col3, a.col4, a.col5, a.col6, a.col7,

         a.col8, a.col9, a.col10, il_view.new_col   FROM a,

       (SELECT b.col2, DECODE(c.col1,NULL,0,1) new_col
        FROM b, c
        WHERE b.col2 = c.col1 (+) ) il_view

  WHERE a.col2 = il_view.col2 ;

This inline view will return all rows from table b this either '0' or '1'. This inline view is joined to table a to create your desired view.

The inline view would also work if a union was used. IE:

       (SELECT b.col2, 0 new_col
           FROM b
           WHERE b.col2 NOT IN (SELECT c.col1 FROM c
        UNION
        SELECT b.col2, 1 new_col
           FROM b
           WHERE b.col2 IN (SELECT c.col1 FROM c
       ) il_view

HTH
  James

In article <84ihps$g3l$1_at_nnrp1.deja.com>,   maylee <mayleel_at_my-deja.com> wrote:
> here is my problem...
>
> I am creating a view with table A with 10 columns col1 - col10.
>
> I have a reference table b that has a foreign key to a . this table
(b)
> is what contains the value b.col2 that I need to use below
>
> I need to add a column new_col to the view that has a 1 or 0 based on
> the following.
>
> new_col is 1 if b.col2 in (select * from c (one column table) and 0
> otherwise.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 31 1999 - 10:51:40 CST