RE: Is it possible to create a sub select where the sub select refreshes itself for each record read?

Darryl,
You get duplicates because Table_A has duplicates.

Darren has already given the answer.

The Query that selects from Table_A must return ONE record for each unique value of FIELD_A.
So you must omit the extra records from TABLE_A yourself.

If all you need is on FIELD_A then you can just do this:
INSERT INTO TABLE_B
SELECT DISTINCT A.FIELD_A FROM TABLE_A A EXCEPTION JOIN TABLE_B B ON B.FIELD_A = A.FIELD_A
;

If you need multiple fields then you can use something like this:


1. Create a unique list of FIELD_A values from TABLE_A, omitting matching values in TABLE_B.

2. Join that back to TABLE_A and select the first record for each FIELD_A value.

INSERT INTO TABLE_B (FIELD_A, FIELD_B, FIELD_C)
SELECT T2.FIELD_A, T2.FIELD_B, T2.FIELD_C
FROM (Select DISTINCT FIELD_A FROM TABLE_A EXCEPTION JOIN TABLE_B ON B.FIELD_A = A.FIELD_A) T1
CROSS JOIN LATERAL (SELECT A.* FROM TABLE_A A
WHERE A.FIELD_A = T1.FIELD_A
ORDER BY SOME_OTHER_FIELD_IN_TABLE_A
FETCH FIRST 1 ROWS ONLY) T2


--
--
--
Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.

From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of a4g atl
Sent: Monday, April 17, 2023 5:43 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Is it possible to create a sub select where the sub select refreshes itself for each record read?


@Darren, the subselect is done once before the select is processed. It does

not go back and check for added records.



Darryl