Re: Insert into table with complex expression

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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