Re: Sql Comparison Statement help needed

On Sep 11, 2014, at 2:22 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 11-Sep-2014 12:08 -0500, RPGLIST wrote:
I have a file with over 18,000 stop off points, They are grouped
according to the pro number and stop off number:

Pro: 198756
Stop: 1

Pro: 198756
Stop: 2

etc.

Within each set I have a field that is used for billing purposes:
BillPnt

Now, all the lines should have the exact same bill point value, it
is a 3 alpha field, it can be blank as well.

I need to select all the records where the <ed: bill point> value
is different from one stop off point to another grouped by the pro
number.

IE:

_BAD_
Pro: 198756
Stop: 1
BillPnt: 707

Pro: 198756
Stop: 2
BillPnt: *Blank

_GOOD_
Pro: 205478
Stop: 1
BillPnt: 200

Pro: 205478
Stop: 2
BillPnt: 200


I need to capture in a new file just the Pro number for those that
have a mismatch IE: BAD.

I can group the records, but I'm not sure how to structure the SQL
statement for the comparison....

Suggestions would be greatly appreciated.

FWiW: The topic seems unrelated to RPG.

Seems the following script may establish a setup that reflects the above givens:

create table SOPs
( Pro integer, Stop dec, BillPnt char(3) not null )
;
insert into SOPs values
( 198756, 1, 707 ), ( 198756, 2, '' )
, ( 205478, 1, 200 ), ( 205478, 2, 200 )
;

As I understand: As a rule, irrespective of /Stop/ for any /Pro/, there must be only one Bill Point value across each unique value of /Pro/. And the data in a new file would identify every /Pro/ for which there is an exception to that rule? If so, then perhaps the following [almost identical to what Scott offered, but omitting the aggregate COUNT data from the result-set]:

create table SOPsBAD as
( select Pro
from SOPs
group by Pro
having count(distinct BillPnt) > 1
) with data

--
Regards, Chuck
--
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.