Re: Insert into table with complex expression

David,

I don't see anything wrong, I suspect the simplified version you posted
doesn't have the same problem as the real one.

In any event, MERGE would be a much better option for this kind of thing...

merge into myfile tgt
using (
select
REGEXP_SUBSTR(otherfield1, :regexp, 1,1,'',1) ctefield1, -- 1st group
from expr
REGEXP_SUBSTR(otherfield2, :regexp, 1,1,'',2) ctefield2 -- 2nd group
from expr
from otherfile
where otherfield2 = 'somevalue'
) src
on tgt.realfield1 = src.ctefield1 and tgt.realfield2 = src.ctefield2
when not matched then
insert (realfield1, realfield2) vales (src.ctefield1, src.ctefield2)
;

Charles



On Thu, Nov 1, 2018 at 10:09 AM David Gibbs <david@xxxxxxxxxxxx> wrote:

Folks:

I need some advice on a somewhat complex SQL statement (at least complex
for me)...

I have an insert statement, with a sub-select statement, that does
includes a number of somewhat complex expression's on the same column
(REGEXP_SUBSTR). I also want to do some comparison's on the expression
results and skip a record if it already exists in the target table.

I thought that a CTE would make things easier.

Here's what I have so far:

insert into myfile (realfield1, realfield2)
with mycte (ctefield1, ctefield2)
as (
select
REGEXP_SUBSTR(otherfield1, :regexp, 1,1,'',1), -- 1st group from expr
REGEXP_SUBSTR(otherfield2, :regexp, 1,1,'',2) -- 2nd group from expr
from otherfile
where otherfield2 = 'somevalue'
)
select ctefield1, ctefield2
from mycte
where ctefield1 <> ' ' and
not exists (
select *
from myfile
where realfield1 = ctefield1 and realfield2 = ctefield2
)
;

The first time I run the statement it works, a record gets inserted.

The second time I run the statement it's not working ... a record gets
inserted. The 'not exists' predicate doesn't appear to be identifying that
the record already exists in the target file.

Any suggestions on what might be going wrong?

Thanks!

david

--
IBM i on Power Systems: For when you can't afford to be out of business!

I'm riding 615 miles (Yes, you read that right) in the American Diabetes
Association's Tour de Cure to raise money for diabetes research, education,
advocacy, and awareness. You can make a tax deductible donation to my ride
by visiting https://mideml.diabetessucks.net.

You can see where my donations come from by visiting my interactive
donation map ... https://mideml.diabetessucks.net/map (it's a geeky
thing).

I may have diabetes, but diabetes doesn't have me!
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

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