× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Thanks Kyle. Worked like a charm.

On Tue, May 11, 2021 at 3:40 PM Kyle Lawson <lawsonk@xxxxxxxxxxxxxxx> wrote:

Using a CTE you can select the records from A that are not in B, then join
to be on FA1 = FB1 and find the distinct records you are looking for.

insert into fileb ( fb1, fb2, fb3, fb4, fb5, fb6 )

with not_in_b as (
select a.*
from filea a
exception join fileb b on a.fa1 = b.fb1 and a.fa2 = b.fb2 and a.fa3 = b.fb3
)

select distinct a.fa1, a.fa2, a.fa3, b.fb4, b.fb5, b.fb6
from not_in_b a
join fileb b on a.fa1 = b.fb1

On Tue, May 11, 2021 at 2:23 PM Vinay Gavankar <vinaygav@xxxxxxxxx> wrote:

Hi,
I need some help with SQL statements in RPG. I have

File A with fields FA1, FA2, FA3

File B with fields FB1, FB2, FB3, FB4, FB5, FB6



If there are no records in File B where FA1=FB1 and FA2=FB2 and FA3=FB3
then I want to insert records into File B with FB1=FA1, FB2=FA2, FB3=FA3
and FB4, FB5, FB6 as DISTINCT values already existing in File B, where
FB1=FA1



For example,

File A has following records:

CL1, CAR1, ACC1

CL1, CAR1, ACC2

CL1, CAR1, ACC3

CL1, CAR2, ACC4

CL1, CAR2, ACC5

CL2, CAR11, ACC11

CL2, CAR12, ACC12



File B has following records:

CL1, CAR1, ACC1, PRD1, QTY1, DS1

CL1, CAR1, ACC1, PRD2, QTY1, DS1

CL1, CAR1, ACC1, PRD2, QTY2, DS2

CL1, CAR1, ACC2, PRD1, QTY1, DS1

CL1, CAR1, ACC3, PRD1, QTY1, DS1

CL1, CAR1, ACC3, PRD2, QTY2, DS2

CL1, CAR2, ACC4, PRD3, QTY3, DS3

CL2, CAR11, ACC11, PRD11, QTY11, DS11

CL2, CAR11, ACC11, PRD13, QTY13, DS13



Since there are no records in File B for CL1:CAR2:ACC5, and
CL2:CAR12:ACC12, I want to insert following records in File B:

CL1, CAR2, ACC5, PRD1, QTY1, DS1

CL1, CAR2, ACC5, PRD2, QTY1, DS1

CL1, CAR2, ACC5, PRD2, QTY2, DS2

CL1, CAR2, ACC5, PRD3, QTY3, DS3

CL2, CAR12, ACC12, PRD11, QTY11, DS11

CL2, CAR12, ACC12, PRD13, QTY13, DS13



Can this be done in a* single SQL statement*?



If not, I can have a RPG routine:

Setll *loval FileA;

Read FileA;

DoW not %eof(FileA);

Setll (FA1:FA2:FA3) FileB;

If not %equal;

*Exec sql to insert records; *

Endif;

Read FileA;

EndDo;


Can someone help with the SQL statements? I know that I can create an
index
on File B with FB1, FB, FB5, FB6 and read the records to get the distinct
values and write the records using regular RPG, but I believe SQL will do
the job more efficiently.


TIA
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com



--
Kyle Lawson
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.