Re: Help w/Normalized VIEW
Date: 1997/12/14
Message-ID: <34945D02.5E45803B_at_wmd.de>#1/1
Jon Derman wrote:
> If I have a table that looks something like this:
>
> SSN VARCHAR2(9)
> COURSE1 VARCHAR2(6)
> COURSE2 VARCHAR2(6)
> COURSE3 VARCHAR2(6)
>
> ...can I simply create a normalized VIEW of that table that looks
> something like this (with 3 times as many rows as the original)?:
>
> SSN VARCHAR2(9)
> COURSE VARCHAR2(6)
>
> Can someone please show me example SQL for creating such a VIEW?
>
> Thanks.
Hi Jon!
You might use a 'union all' like this:
create or replace your_view
( ssn,
course
)
as
select
ssn,
course1
from your_table
where course1 is not null
union all
select
ssn,
course2
from your_table
where course2 is not null
union all
select
ssn,
course3
from your_table
where course3 is not null
;
If you don't want to get dublicate lines use 'union' instead of 'union
all'.
If you want to get the NULL values, remove the where clauses.
But be warned: These kind of views are real performace-killer if they where used it in joins!
Hope it help's!
Jörn
-- _________________________________________________________________________________ Jörn Fieg WMD GmbH to replay me, replace nospam by fieg Hamburg / GermanyReceived on Sun Dec 14 1997 - 00:00:00 CET