Select cas.iCasacApptID, cas.cEncCatID
>From CasacAppt cas, Client cli, CasacApptRelationship rel, CMMonthlyContact
con, CMAppt cm
Where cas.iCasacApptID = (Select Max(rel.iCasacApptID) )
AND con.iEmpID = cm.iCMApptID
AND cm.bActive = 1
Group By cas.iCasacApptID, cas.cEncCatID
I keep getting an error saying: An aggregate may not appear in the WHERE
clause unless it is in a subquery contained in a HAVING clause or a select
list, and the column being aggregated is an outer reference.
Am I getting the syntax wrong or is this query not possible...I've played
around with it a lot and cnat get it to work.
Any suggestions?
Thanks,
Julie
>From: "John" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: CF-Talk <[EMAIL PROTECTED]>
>Subject: RE: SQL Newbie - Help!!!!!
>Date: Tue, 15 May 2001 14:07:28 -0500
>
>I think this will give you what you want.
>
>select top 1 t2.pk_CasacAppt from tblCasacCM left outer join tblCasac t2
>on t1.pk_CasacAppt = t2.pk_CasacAppt
>left outer join tblContacts t3 on t1.pk_CMAppt = t3.CMAppt
>where pk_uniqueID = #Variable#
>order by t2.CasacDate desc
>
>John Hancock
>
>-----Original Message-----
>From: Julie Clegg [mailto:[EMAIL PROTECTED]]
>Sent: Tuesday, May 15, 2001 1:01 PM
>To: CF-Talk
>Subject: SQL Newbie - Help!!!!!
>
>
>Hello,
>
>I have a large sql query I need to write and am having some problems with
>it. I have broken it down into several sections...the fist section I
>cannot
>figue out involves 3 table which I have listed below along with the main
>fields I need to deal with in parenthasis:
>
>tblContacts
>(pk_uniqueID, CMAppt)
>
>tblCasacCM
>(pk_CMAppt, pk_CasacAppt)
>
>tblCasac
>(pk_CasacAppt, CasacDate)
>
>
>In the tblCasac, there are many pk_CasacAppt's that relate to only one
>CMAppt. I need to capture the most recent pk_CasacAppt(using CasacDate)
>for
>a particular CMAppt.
>Hope this all makes sense,,,any suggestions would be greatly appreciated...
>
>
>TIA,
>
>Julie
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
