RE: SQL... MAX AND COUNT?

Not saying it wouldn't work, but seems contrary to what I recall of the
HAVING clause.

"The HAVING clause specifies an intermediate result table that consists
of those groups of R for which the search-condition is true. R is the
result of the previous clause of the subselect. If this clause is not
GROUP BY, R is considered a single group with no grouping expressions."

As I understand it, unlike WHERE which test each row before selection
into the result sets, HAVING tests after the subselect has built its
result set.

To use HAVING in this case, I think you'd want something like:

 SELECT LDQDAT, COUNT(LDSUBC) INTO :MAXDATE, :WKCOUNT
 FROM BANKHEDR INNER JOIN LRDETAIL 
         ON QHSTCD = LDSTCD AND QHAREA = LDAREA AND QHBANK =    
         LDBANK AND LDDGRP = QHHGRP AND LDCATC = :WKCATCD 
 WHERE QHHGRP = :WKGROUP AND QHRSTA = 'A' AND QHMAIN = :WKMAIN    
 GROUP BY LDQDAT              
 HAVING LDQDAT = MAX(LDQDAT)                                        

However, you'll note that if it works the intermediate result set
(before the having is applied) contains a lot of rows that will be
discarded by the HAVING.

Instead, I'd do it this way:
 SELECT COUNT(LDSUBC) INTO :WKCOUNT
 FROM BANKHEDR INNER JOIN LRDETAIL 
         ON QHSTCD = LDSTCD AND QHAREA = LDAREA AND QHBANK =    
         LDBANK AND LDDGRP = QHHGRP AND LDCATC = :WKCATCD 
 WHERE QHHGRP = :WKGROUP AND QHRSTA = 'A' AND QHMAIN = :WKMAIN    
   and LDQDAT = ( SELECT MAX(LDQDAT)
                  FROM LRDETAIL
                )



HTH,
                                         

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Carel Teijgeler
Sent: Tuesday, January 02, 2007 4:40 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: SQL... MAX AND COUNT?

Jay,

You should use the GROUP BY ... HAVING ... combination like:

C/EXEC SQL                                                         
C+ SELECT COUNT(LDSUBC) INTO :WKCOUNT FROM BANKHEDR INNER JOIN     
C+ LRDETAIL ON QHSTCD = LDSTCD AND QHAREA = LDAREA AND QHBANK =    
C+ LDBANK AND LDDGRP = QHHGRP AND LDCATC = :WKCATCD WHERE QHHGRP = 
C+ :WKGROUP AND QHRSTA = 'A' AND QHMAIN = :WKMAIN    

C+ GROUP BY QHSTCD              
C+ HAVING LDQDAT = MAX(LDQDAT)                                        

C/END-EXEC                                                         

Regards,
Carel Teijgeler

*********** REPLY SEPARATOR  ***********

On 2-1-2007 at 15:11 Jay Vaughn wrote:

The precompiler doesn't like the "AND LDQDAT = MAX(LDQDAT)" in the

following 

statement, but I ONLY need those latest dates considered in 

the count...

How do I restructure this query to consider that?

C/EXEC SQL                                                         
C+ SELECT COUNT(LDSUBC) INTO :WKCOUNT FROM BANKHEDR INNER JOIN     
C+ LRDETAIL ON QHSTCD = LDSTCD AND QHAREA = LDAREA AND QHBANK =    
C+ LDBANK AND LDDGRP = QHHGRP AND LDCATC = :WKCATCD WHERE QHHGRP = 
C+ :WKGROUP AND QHRSTA = 'A' AND QHMAIN = :WKMAIN                  
C+ AND LDQDAT = MAX(LDQDAT)                                        
C/END-EXEC                                                         

-- 
This is the RPG programming on the AS400 / 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.