Re: JOIN with UNION

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

If the files have the same format structure ( field names and data 
types ) you can name them both in the PFILE keyword. The CRTLF 
command  will combine the 2 into one LF. You have to use a key, 
however. And SQL will be able to use it, since it has only one 
format. If you do not use an ORDER BY it just runs. If you use an 
ORDER BY, even the same as the LF, a temporary copy is made. Probably 
similar overhead to using the UNION.

Interesting, eh?

BTW, you can get this information simply by turning on debug (STRDBG) 
and running the SQL. The optimizer will put its decision process into 
messages in your job log. In this case, one of the messages, in its 
second-level text, said that the copy was made because the LF was 
based on multiple PFs.

At 03:43 AM 5/25/2006, you wrote:

> Hi
>
> How to create "logical" files which will be equivalent for SQL code:
>
> insert into LIB/FILEALL
> SELECT * FROM FILE1 UNION ALL SELECT * FROM FILE2
>
> With Join statement I have to use some condition, so how to union two or
> more files?
>
> I can do it by sql but I would like to have FILEALL with always "fresh"
> records?
>
> Regards
>
> Tomasz Skorza
>
> --
> 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.