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

Though not stated, presumably it was learned that those prior quoted suggestions were of no help [included below; I am top-posting], thus the followup with additional explanation?

Those queries likely would want to be formed as a COUNT aggregate with HAVING, built over the results of the alluded extended grouping; modified to find the groups that resulted in more than one row for any one cmlPro#. As they are written, those variations of the same query will identify any /good/ rows as /bad/ rows, because any cmlPro# with multiple rows all having identical tempFlag values will have a count greater than one; i.e. the queries just test for a count greater than one. And FWiW, the COUNT(tempflag) is, except for omission of counting the NULL values, the same as COUNT(*); given no mention was made of the NULL value, I suspect that NULL values are not a part of the scenario.

select cmlPro#, count(*)
from (
Select CmlPro#, tempflag, count(tempflag) as cnt
from PBF1809
Where cmlShipDt > 20140101 and CmlShipDt < 20140901
group by CmlPro#, tempflag
/* having count(tempflag) > 1; */
) as SQ
group by cmlPro#
having count(*) > 1

or using the 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#, count(*)
from FlgCnt
group by cmlPro#
having count(*) > 1

While those two variants of the same query [those rewritten above to aggregate the aggregate results] should satisfy the stated requirement, yet they are neither pretty nor likely generally efficient. AFaIK the use of a DISTINCT values COUNT suggested in my prior reply should be better than doubly grouping\aggregating, both in readability and efficiency in query implementation.

Regards, Chuck

On 11-Aug-2014 11:31 -0500, RPGLIST wrote:

The records will have a temp flag, it should be the same value
though. If its a non temp controlled load it'll be 0, otherwise it'll
have a temp - what I don't want is a record with multiple stops (the
first count) that have different temps... that's a major issue.

On 11-Aug-2014 11:45 -0500, Steve Stevens wrote:

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;

On 11-Aug-2014 11:33 -0500, Robert Rogerson wrote:

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.

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?