> 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
