Friday, 27 March 2015

Using multiple columns in where clause and set based exception

Jeepers, I have no idea how to define this, but I’m working out records that are in one table and not in another based upon the primary key.  I then want to use this list to feed a insert statement to fix the missing records.

I cannot use the entire tuple, as there is date/time stamps that will say they are all different.

The answer is simple, but I just needed the syntax help.

The “real” use case scenario is synchronising F9862 between two different systems.  We are copying a pathcode and need to make sure all of the OL records are going to exist.  This is easy for F9861 – as it’s pathcode specific.  It’s a little harder when dealing with F9862, F9863 and F9865 – as they are not pathcode specific.

--find the missing records
select * from JDEDBA.F9862 t3
where (siobnm, sifctnm, simd) in (
select SIOBNM,SIFCTNM, SIMD from JDEDBA.F9862
minus
select SIOBNM,SIFCTNM, SIMD from OL910.F9862);

--insert them into F9862 - OL one
insert into ol910.f9862
select * from JDEDBA.F9862 t3
where (siobnm, sifctnm, simd) in (
select SIOBNM,SIFCTNM, SIMD from JDEDBA.F9862
minus
select SIOBNM,SIFCTNM, SIMD from OL910.F9862);

1 comment:

Dave said...

Hi Shannon,

Not sure which database this is for, but you could try something like this:

select * from JDEDBA.F9862 t3
where siobnm+sifctnm+simd in
(
select siobnm+sifctnm+simd from JDEDBA.F9862
)
and siobnm+sifctnm+simd not in
(
select siobnm+sifctnm+simd from OL910.F9862
);

Dave

Extending JDE to generative AI