× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Vern,
My bad I got typing too fast, irritated today, and did not realize I had
put it down incorrectly. It should be Greater than or Equal to on the
date, and NOT EQual to Pluto.

The code is correct, I typed out the specs incorrectly.


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.





As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.