Re: views...

A copy of this was sent to maylee <mayleel_at_my-deja.com> (if that email address didn't require changing) On Fri, 31 Dec 1999 15:36:22 GMT, you 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.

do you mean something like this:

tkyte_at_8.0> create table a ( c1 int, c2 int unique, c3 int ); Table created.

tkyte_at_8.0> create table b ( c2 int references a(c2) ); Table created.

tkyte_at_8.0> create table c ( x int );
Table created.

tkyte_at_8.0> insert into a values ( 1, 1, 1 );
tkyte_at_8.0> insert into a values ( 1, 2, 1 );
tkyte_at_8.0> insert into a values ( 1, 3, 1 );

tkyte_at_8.0> insert into b values ( 1 );
tkyte_at_8.0> insert into b values ( 2 );

tkyte_at_8.0> insert into c values ( 1 );

tkyte_at_8.0> create or replace view v
  2 as
  3 select a.c1, a.c2, a.c3,

            decode( x, null, 0, 1 ) "Corresponding b.c2 is in C"   4 from a, b, c
  5 where a.c2 = b.c2(+)
  6 and b.c2 = c.x (+)
  7 /

View created.

tkyte_at_8.0>
tkyte_at_8.0> select * from v
  2 /

        C1 C2 C3 Corresponding b.c2 is in C

---------- ---------- ---------- --------------------------
         1          1          1                          1
         1          2          1                          0
         1          3          1                          0

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Dec 31 1999 - 09:59:13 CST