Union in Views

Delores Swedlund

Union in Views
We are at version 6 of DB2. It appears 'union' can't be used to create a view. Can anyone suggest an alternate solution?

Here's my situation: I have 2 tables (table names OLD_DATA and NEW_DATA, for example) with identical structures. The rows on each table are mutually exclusive. I want a view (view name ALL_DATA) that brings both together, and that view also would have exactly the same columns as the 2 tables. Can someone suggest SQL that doesn't use UNION to accomplish this?

Thanks.

Delores Swedlund
Lands' End, Inc.
Dodgeville, WI

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Sanjay (Exchange) Jain

Re: Union in Views
(in response to Delores Swedlund)
Use FULL OUTER JOIN with COALESCE function.



-----Original Message-----
From: Swedlund, Delores B [mailto:[login to unmask email]
Sent: Wednesday, December 24, 2003 12:05 PM
To: [login to unmask email]
Subject: Union in Views



We are at version 6 of DB2. It appears 'union' can't be used to create a
view. Can anyone suggest an alternate solution?



Here's my situation: I have 2 tables (table names OLD_DATA and NEW_DATA,
for example) with identical structures. The rows on each table are mutually
exclusive. I want a view (view name ALL_DATA) that brings both together,
and that view also would have exactly the same columns as the 2 tables. Can
someone suggest SQL that doesn't use UNION to accomplish this?



Thanks.



Delores Swedlund

Lands' End, Inc.

Dodgeville, WI

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dale Hardy

Re: Union in Views
(in response to Sanjay (Exchange) Jain)
Try something like this

CREATE VIEW BP_READINGS_V <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

( BP_READING_KEY

,BP_READ_TYPE_KEY

,BP_READING )

AS

SELECT

COALESCE(T1.BP_SYSTOLIC_KEY , T2.BP_DIASTOLIC_KEY,0)

AS BP_READING_KEY

,COALESCE(T1.BP_READ_TYPE_KEY , T2.BP_READ_TYPE_KEY,0)

AS BP_READ_TYPE_KEY

,COALESCE(T1.BP_SYSTOLIC , T2.BP_DIASTOLIC)

AS BP_READING

FROM

(SELECT

BP_SYSTOLIC_KEY

,BP_READ_TYPE_KEY

,BP_SYSTOLIC

FROM BP_SYSTOLICS

,BP_READING_TYPES

WHERE BP_READ_TYPE_KEY = 1) AS T1

FULL OUTER JOIN

(SELECT

BP_DIASTOLIC_KEY

,BP_READ_TYPE_KEY

,BP_DIASTOLIC

FROM BP_DIASTOLICS T023

,BP_READING_TYPES

WHERE BP_READ_TYPE_KEY = 2) AS T2

ON T1.BP_SYSTOLIC_KEY = T2.BP_DIASTOLIC_KEY

AND T1.BP_READ_TYPE_KEY = T2.BP_READ_TYPE_KEY



-- SINCE THE T1 KEYS NEVER EQUAL THE T2 KEYS THE RESULT IS A UNION

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Swedlund, Delores B
Sent: Wednesday, December 24, 2003 12:05 PM
To: [login to unmask email]
Subject: Union in Views


We are at version 6 of DB2. It appears 'union' can't be used to create a view. Can anyone suggest an alternate solution?

Here's my situation: I have 2 tables (table names OLD_DATA and NEW_DATA, for example) with identical structures. The rows on each table are mutually exclusive. I want a view (view name ALL_DATA) that brings both together, and that view also would have exactly the same columns as the 2 tables. Can someone suggest SQL that doesn't use UNION to accomplish this?

Thanks.

Delores Swedlund
Lands' End, Inc.
Dodgeville, WI
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Colin M Fay

Re: Union in Views
(in response to Dale Hardy)
It would look something like this :



CREATE VIEW NEW_VIEW AS

SELECT COALESCE(A.C_ROL_IDV,B.C_ROL_IDV) AS C_ROL_IDV

FROM

STRESS.TETS010_SYSUSR A

FULL OUTER JOIN

ACPT.TETS010_SYSUSR B

ON A.I_LGN= B.I_LGN

;



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Swedlund, Delores B
Sent: Wednesday, December 24, 2003 12:05 PM
To: [login to unmask email]
Subject: Union in Views



We are at version 6 of DB2. It appears 'union' can't be used to create
a view. Can anyone suggest an alternate solution?



Here's my situation: I have 2 tables (table names OLD_DATA and
NEW_DATA, for example) with identical structures. The rows on each
table are mutually exclusive. I want a view (view name ALL_DATA) that
brings both together, and that view also would have exactly the same
columns as the 2 tables. Can someone suggest SQL that doesn't use UNION
to accomplish this?



Thanks.



Delores Swedlund

Lands' End, Inc.

Dodgeville, WI

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". If you will be out of
the office, send the SET DB2-L NO MAIL command to
[login to unmask email] The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Roger Miller

Re: Union in Views
(in response to Colin M Fay)
This sounds like another good reason for getting to V7. If you see Jerry
Bustamente or Mike Phillips when you check on the timing, please relay
season's greetings from me.

Roger Miller


On Wed, 24 Dec 2003 11:05:26 -0600, Swedlund, Delores B
<[login to unmask email]> wrote:

>We are at version 6 of DB2. It appears 'union' can't be used to create a
view. Can anyone suggest an alternate solution?
>
>Here's my situation: I have 2 tables (table names OLD_DATA and NEW_DATA,
for example) with identical structures. The rows on each table are
mutually exclusive. I want a view (view name ALL_DATA) that brings both
together, and that view also would have exactly the same columns as the 2
tables. Can someone suggest SQL that doesn't use UNION to accomplish this?
>
>Thanks.
>
>Delores Swedlund
>Lands' End, Inc.
>Dodgeville, WI

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: Union in Views
(in response to Roger Miller)
How about a FULL OUTER JOIN? It should do the trick (join on a dummy
column or use ON 1=1).

Thanks,
Suresh


>From: Roger Miller <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: Union in Views
>Date: Wed, 31 Dec 2003 15:51:28 -0600
>
>This sounds like another good reason for getting to V7. If you see Jerry
>Bustamente or Mike Phillips when you check on the timing, please relay
>season's greetings from me.
>
>Roger Miller
>
>
>On Wed, 24 Dec 2003 11:05:26 -0600, Swedlund, Delores B
><[login to unmask email]> wrote:
>
> >We are at version 6 of DB2. It appears 'union' can't be used to create a
>view. Can anyone suggest an alternate solution?
> >
> >Here's my situation: I have 2 tables (table names OLD_DATA and NEW_DATA,
>for example) with identical structures. The rows on each table are
>mutually exclusive. I want a view (view name ALL_DATA) that brings both
>together, and that view also would have exactly the same columns as the 2
>tables. Can someone suggest SQL that doesn't use UNION to accomplish this?
> >
> >Thanks.
> >
> >Delores Swedlund
> >Lands' End, Inc.
> >Dodgeville, WI
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". If you will be out of the office, send the SET
>DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
>can be reached at [login to unmask email] Find out the latest on
>IDUG conferences at http://conferences.idug.org/index.cfm

_________________________________________________________________
Make your home warm and cozy this winter with tips from MSN House & Home.
http://special.msn.com/home/warmhome.armx

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm