Re: Embedded SQL statement incorrect

2 things I see - in your first SELECT you have

Ldd >= 20140401 - your spec #3 says "greater than"

And you have LDO <> 'PLUTO' where your spec #2 says "equal to"

That's enough - I'm off to sort of a picnic

HTH
Vern

On 9/18/2014 4:21 PM, RPGLIST wrote:

I'm trying to find some mismatched records within our load file, I
thought
I had it with some help from a few others, but we're really not getting
the records we know should be there.

What I mean by that is this, I have two examples that I have manually
created and they are not getting picked up by the SQL statement.

So here is what I'm doing and maybe someone can point out where I'm off.

First step was to select all records from our load file that met the
following criteria:

1. EDB (Edi submiited transaction) is blank
2. final user (programmatically processed load) is equal to 'PLUTO'
3. Load date is greater than 04/01/2014
4. Final status is equal to D, F, H, R, N, P, U

Here is the select and insert statement for this:

Exec Sql
Insert into Bleed
(Pro#, ProLn#, Ldo, Edb, Ldd, F2, Pnt,
Emp, Bcd, Pid, Fdt)
Select Pro#, ProLn#, Ldo, Edb, Ldd, @F2, Pnt,
Emp, Bcd, Pid, Fdt
From LoadL05
Where
Edb = '' and Ldo <> 'PLUTO'
and Ldd >= 20140401 and F2 IN
('D', 'F', 'H', 'R', 'N', 'P', 'U');

The second step is to look at all loads that have MULTIPLE stop offs,
and
find those records that have a mismatch within the load where PNT is
different from one line to the next. IE: Load 108976 line 1 has a PNT
value of 307 and line 2 has a value of 519. That's a problem.

Here is the statement I'm using to select these records:

Exec Sql
Insert into BadRec
(Pro#)
Select Pro#
From Bleed A
Where exists (Select 1 from Bleed B where A.Pro# = B.Pro#
and A.Pnt <> B.Pnt)
Group by Pro#;


The final step is quite long and I'd rather not post it yet unless
its really needed...I'm concerned because I as I've said I have two
loads that I manually created that are not being picked up.


Suggestions or thoughts?

I don't want to have to write a cycle program to do the second part
if I can help it......

Dutch

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.