Whew! This is an interesting query, Amanda.
There are a couple of issues here. Your loop should be outside the query,
otherwise what you'll end up with is multiple select statements within the
query. My guess is that's not really what you're after.
As to the other issue, you need to use an outer join, so that if there is no
value in the joined table, you still receive a record. I think you're trying
to do something like this:
<cfloop query="treatCount">
<cfif treatcount.TreatCount gt 6>
<cfquery name="QRYtp"
datasource="#request.projectAceDb#">
Select cas.iClientID ,
cd1.vCodeDecode as TreatSelect,
'#treatCount.TreatProg#' as TrueProg,
'#treatCount.HRACode#' as TrueHRA
>From (CasacAppt cas LEFT OUTER JOIN
CasacApptTreatSelect cats ON cats.icasacapptid = cas.icasacapptid),
TreatmentProg tp,
CodeDecode cd1,
Client cl
Where cas.iClientID = cl.iClientID
AND '#treatCount.TreatProgID#' = cas.iTreatProgID
AND cats.cTreatSelectID = cd1.icodedecodeID
AND cas.dCasacDate >=
'#variables.stFieldNames.DBeginDate#'
AND cas.dCasacDate <=
'#variables.stFieldNames.dEndDate#'
AND cas.dCasacDate > '#request.dContractDate#'
</cfquery>
</cfif>
</cfloop>
There are going to be some issues with your other tables, as I don't know
the relationships there, and I always have to play with the database query
builder in Access (I'm assuming this is access). I'm better at writing sql
for oracle, but this should get you started.
Deanna Schneider
Interactive Media Developer
[EMAIL PROTECTED]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
