RE: SQL... MAX AND COUNT?

Jay,

MAX is a column function not a scalar function.  That means you have to
use it with the group by clause.  I'm not sure how you would group your
result set, QHHGRP maybe?  Anyway you should have something similar to
this in your statement.

Select QHHGRP, count(ldsubc) ... Group by QHHGRP having ldqdat =
max(ldqdat)

The having clause is like using where on your grouped results.

HTH,

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jay Vaughn
Sent: Tuesday, January 02, 2007 3:12 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL... MAX AND COUNT?

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                                                        





Thanks,


Jay Vaughn
iSeries Senior Programmer/Analyst
RateWatch
jayv@xxxxxxxxxxxxxx
251-633-8929
--
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.

Privileged and Confidential.  This e-mail, and any attachments there to, is 
intended only for use by the addressee(s) named herein and may contain 
privileged or confidential information.  If you have received this e-mail in 
error, please notify me immediately by a return e-mail and delete this e-mail.  
You are hereby notified that any dissemination, distribution or copying of this 
e-mail and/or any attachments thereto, is strictly prohibited.