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

On 11-Aug-2014 10:09 -0500, 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?

The count of the DISTINCT values for the cmlTempFlg would not equate with one, if not all cmlTempFlg values were the same within the cmlPro# grouping. Thus I believe the following might satisfy the stated requirements?:

Select CmlPro#
, count(*) as cnt
, count(distinct cmlTempFlg) as dcnt
from PBF1809
Where cmlShipDt > 20140101
and CmlShipDt < 20140901
group by CmlPro#
having count(*) > 1
and count(distinct cmlTempFlg) <> 1