RE: SQL, Logical Files, Unions & Indexes.

Yes, it is possible to use Union in common table expressions (CTE), at least as
of V5R3.

With
S1 as
( Select ...
From a1
Union
Select ...
From a2
Union
Select ...
From a3
),

S2 as
( Select ...
From b1
Union
Select ...
From b2
Union
Select ...
From b3
)

Select S1.*, S2.*

From S1 inner join S2 on (S1.key=S2.key)

Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Caura
Sent: Wednesday, April 04, 2007 3:47 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL, Logical Files, Unions & Indexes.


Hi,

One more question :

Can multiple Union-series be joined together in the following way ?

Select 1 union Select 2 union Select 3 ...

joined with

Select a union Select b union select c ... ?

Probably I have to use

Select (joinX) union Select (joinY) union Select (joinZ ) etc , have I ?

Both ways, we will end up with something hardly readably
(contrary to LFs over multiple Tables that furthermore are referenced in a
oldfashioned FRF) :
One select takes 2000 chars and more, and I have to unionize 20 regions,
this ends up with ...

I am sure you're right about the store procedures,
but this means our new setup will not work, and we have to refactor the
whole thing ...

-


----- Original Message -----
From: "Alan Campin" <Alan.Campin@xxxxxxxxxxxxxxxx>
To: <midrange-l@xxxxxxxxxxxx>
Sent: Wednesday, April 04, 2007 12:38 AM
Subject: Re: SQL, Logical Files, Unions & Indexes.

<snip>
I will try to live with this, but the problem is that a Union is a View
.
And one cannot create Joins over 2 or more Views, can you?
</snip>

Union are not views. They are a way to run individual SQL statements and
have the result of each query stuck together. They can in a ordinary SQL
statement or in a view.

Select 1
Union
Select 2
Union
Select 3

Etc.

The only requirement is the output of each query must be compatible, ie
the same type and size for each field output.

I still am wondering if that cannot just be done in a stored procedure.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.