RE: Enbedded SQL In RPGLE

Agreed. Your program will perform better if you use UNION ALL and account for duplicates in the program logic. Utilizing embedded SQL to select distinct records in union statements may be slow and painful. You could select the results into a temporary table and select distinct records on-the-fly, but it will increase I/O, and the user would need to have the privilege to create temporary tables. This technique is used primarily when a single SQL statement will not suffice. It is useful, but I personally tend to shy away from creating temporary tables in user space for security purposes.

Tom Armbruster

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Thursday, October 29, 2009 10:54 AM
To: 'RPG programming on the IBM i / System i'
Subject: AW: Enbedded SQL In RPGLE

UNION ALL will list all rows returned through both sub-Selects. Duplicates
are not removed
UNION (or UNION DISTINCT) shows duplicates only once.

For performance issues, UNION (Distinct) should only be used if needed,
otherwise UNION ALL will perform better.
When using UNION (Distinct) each row must be completely checked, if it is
already returned or not.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von James Perkins
Gesendet: Thursday, 29. October 2009 16:51
An: RPG programming on the IBM i / System i
Betreff: Re: Enbedded SQL In RPGLE

Mike,
UNION ALL can be really important. For instance in a recursive query you
have to use UNION ALL. Also, if you want to see information from EVERY table
in your union, you have to use UNION ALL.

Generally speaking I use UNION more than UNION ALL, but it definitely has
it's place.

--
James R. Perkins
http://twitter.com/the_jamezp


On Thu, Oct 29, 2009 at 08:36, Mike <koldark@xxxxxxxxx> wrote:

I have always been confused on why use UNION ALL vs. UNION. Have you just
tried UNION?

--
Mike Wills
http://mikewills.info
P: (507) 933-0880 | Skype: koldark


On Thu, Oct 29, 2009 at 10:24 AM, Marc Couture <couturem@xxxxxxxxxxx>
wrote:

I Have an error, and can't figure out ! All the 3 files has same
structure.

*---------------------------------------------------------------------------
-----------------*

* Get BI Orders From OEHLDHDR, OEHDRBU or HSTHDRBU
*

*---------------------------------------------------------------------------
-----------------*

C Get_OeHldHdr Begsr

*

C/Exec Sql

C+ Select HordNo

SQL0109 UNION Clause not allowed

C+

C+ Into :XhOrdNo

C+

C+ From OeHldHdr

C+

C+ Where SubStr(Ech.hcpsn,3,10) = :BhInva

C+ Union All

C+

C+ Select HordNo

C+

C+ Into :XhOrdNo

C+

C+ From OeHdrBu

C+

C+ Where SubStr(Ech.hcpsn,3,10) = :BhInva

C+

C+ Union All

C+

C+ Select HordNo

C+

C+ Into :XhOrdNo

C+

C+ From HstHdrBu

C+

C+ Where SubStr(Ech.hcpsn,3,10) = :BhInva

C+

C+

C/End-Exec




Best Regards,



Marc

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.