Re: Insert records using SQL

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