Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is it possible for me to create such a view?

Re: Is it possible for me to create such a view?

From: Ivar Stolfot <iff_at_online.ee>
Date: Wed, 7 Apr 1999 13:24:31 +0300
Message-ID: <370b31ec$0$8163@diablo.uninet.ee>


> Hi:
> I am just beginnin to learn sql and oracle.
> I am now having two tables:
> 1. create table z1(z1_c1 number,z1_c2 number)
> 2. create table z2(z2_c1 number)
>
> In z1:
> 1 90
> 2 150
> 3 200
>
> In z2:
> 2
> 7
> 8
> 9
>
> I am now trying to create a view from z1 whose second column's value is
> decided by some conditions.
> 1. create a view z1_view1 whose second column's
> value is TRUE if z1's second column's valuse is less then 100, else
FALSE.
> In my case, the out put should be:
> z1_view1:
> 1 TRUE
> 2 FALSE
> 3 FALSE
CREATE OR REPLACE VIEW z1_view1 AS
SELECT z1_c1, 'TRUE'
FROM z1
WHERE z1_c2>100
UNION ALL
SELECT z1_c1, 'FALSE'
FROM z1
WHERE z1_c2<100

>
> 2. create a view z1_view2 whose second column's value is TRUE if z1's
first
> column's value exists in z2, else FALSE. In my case, the output should
be:
> 1 FALSE
> 2 TRUE
> 3 FALSE
CREATE OR REPLACE VIEW z1_view2 AS
SELECT z1.z1_c1, Decode(z2.c1,NULL,'FALSE','TRUE') FROM
    z1, z2
WHERE
   z1.z1_c1=z2.z2_c2(+)



Iff Received on Wed Apr 07 1999 - 05:24:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US