Re: Insert records using SQL

[Kyle Lawson <lawsonk@xxxxxxxxxxxxxxx>]

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