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