Re: Doppleganger column names

From: Claude-Sébastien Jean <csjean_at_logimens.com>
Date: 1997/07/25
Message-ID: <33D8C761.B4A6ECF5@logimens.com>#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;
>
> I would be very very grateful indead, If you could give me a solution
> even if it is by neither of these ways
>
> SMILE IF YOU BRAIN IS ABOUT TO EXPLODE DUE TO A LARGE
> NUMBER OF ALPHAs AND BETAs FLYING AROUND
> AT GREAT SPEED INSIDE
> YOUR CRANIUM
> SMILEY
 
> PS answer in the newsgroup.

You can allways use the DECODE function:

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; I hope this helps

--
CSJ
" And were I so tall as to reach the pole,
  Or to grasp the ocea at a span,
  I must be mesured  by my soul.
  The mind is the standard of the man. "
  Dr. Martin Luther King Jr.

Received on Fri Jul 25 1997 - 00:00:00 CDT