RE: Could use some assistance with a multi count SQL statement

If it doesn't you might be able to use a CTE:

With FlgCnt as (
Select CmlPro#, tempflag, count(tempflag) as cnt
from PBF1809
Where cmlShipDt > 20140101 and CmlShipDt < 20140901
group by CmlPro#, tempflag
)
select CmlPro#, tempflag, cnt
from FlgCnt
where cnt > 1;

Steve

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert
Rogerson
Sent: Monday, August 11, 2014 12:33 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Could use some assistance with a multi count SQL statement

Would this work?

Exec SQL Declare C1 Cursor
For Select CmlPro#, tempflag, count(tempflag) as cnt
from PBF1809
Where cmlShipDt > 20140101 and CmlShipDt < 20140901
group by CmlPro#, tempflag
having count(tempflag) > 1;

This would return the Pro(s) having multiple tempflags.

Rob



On 8/11/2014 11:09 AM, RPGLIST wrote:

I need to run a query (SQL) to find some records that are bad.

I need to find all freight movements with multiple stops that have a
temp flag that is different on the same pro number. I can get the first

part:

Exec SQL Declare C1 Cursor
For Select CmlPro#, count(*) as cnt
from PBF1809
Where cmlShipDt > 20140101 and CmlShipDt < 20140901
group by CmlPro# having count(*) > 1;

What I need to figure out is how to add in the same statement if
possible, where CmlTempFlg is different for any of those pro numbers?

Example:

Pro TempFlg
__________________
548798 32
548798 32
548798 58
548798 -5
...

All the records should have the same temp flg.

Suggestions?

--
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.

--
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.