Re: Doppleganger column names

From: Marc Billiet <Marc.Billiet_at_alcatel.be>
Date: 1997/07/23
Message-ID: <33D5E14C.21E8@alcatel.be>#1/1

David Steele wrote:
> I have 3 views created In SQL. They all have the same Column names eg.
> View called A_VIEW
> ALPHA BETA GAMMA DELTA
> 24 0 0 Alex
> 32 0 0 Jason
> View called B_VIEW
> ALPHA BETA GAMMA DELTA
> 0 2 0 Phil
> View called C_VIEW
> ALPHA BETA GAMMA DELTA
> 0 0 1 Bishop
> 0 0 19 Mike
> I would like to combine them into 1 VIEW looking like
> View Called ALL_VIEWS
> ALPHA BETA GAMMA DELTA
> 24 0 0 Alex
> 32 0 0 Jason
> 0 2 0 Phil
> 0 0 1 Bishop
> 0 0 19 Mike
> IF THIS IS NOT POSSIBLE IS THERE AN IF COMMAND, AS THE 3 TABLES
> ORIGINALLY CAME FROM 1 TABLE LIKE
> Called for example NAMES
> DELTA TYPE VALUE
> Alex A 24
> Jason A 32
> Phil B 2
> Bishop C 1
> Mike C 19
> SO IS THERE A COMMAND THAT WORKS LIKE
> CREATE VIEW ALL_VIEWS
> AS SELECT (IF TYPE='A' THEN to_char(VALUE) ELSE to_char(0)) ALPHA,
> AS SELECT (IF TYPE='B' THEN to_char(VALUE) ELSE to_char(0)) BETA,
> AS SELECT (IF TYPE='C' THEN to_char(VALUE) ELSE to_char(0)) GAMMA,
> DELTA,
> FROM NAMES;
>

There are two ways to do this :

The first (you might also use 'union all' instead of 'union', which is in fact faster):

create view ALL_VIEWS as
  select Alpha, Beta, Gamma, Delta
  from A_VIEW
  union
  select Alpha, Beta, Gamma, Delta
  from B_VIEW
  union
  select Alpha, Beta, Gamma, Delta
  from C_VIEW;

The second way is by starting from the table :

create view All_Views as

  select decode(Type,'A',To_Char(Value),'0') Alpha,
         decode(Type,'B',To_Char(Value),'0') Beta,
         decode(Type,'C',To_Char(Value),'0') Gamma,
         Delta

  from Names;

or (if Alpha, Beta and Gamma may be numbers) :

create view All_Views as

  select decode(Type,'A',Value,0) Alpha,
         decode(Type,'B',Value,0) Beta,
         decode(Type,'C',Value,0) Gamma,
         Delta

  from Names;

The decode-function acts as the if-command you asked for. If the Type is 'A', Alpha equals Value, otherwise it's 0. See the manual for a better explanation.

Marc Billiet Received on Wed Jul 23 1997 - 00:00:00 CDT