Re: Help w/Normalized VIEW

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 / Germany