If in Oracle, you do this by using the NVL function which allows you to
specify a replacement value for a column if it's a null. Quite often you'd
use it to convert nulls to a zero for calculations.
Select column1, column2..., nvl(cats.iCasacApptID, 'None')
>From table etc. etc.
-----Original Message-----
From: Rice, John J [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 21, 2001 4:15 PM
To: CF-Talk
Subject: RE: Cf in Select Stmt help
if you are using Oracle use this type of syntax:
AND cas.iCasacApptID = cats.iCasacApptID(+)
The (+) makes an outer join where cats.iCasacApptID may or may not have a
match for cas.iCasacApptID. So all records from cas will come back. A null
will be in the cats column where there was not a match.
If you are in another dbms;
>From CasacAppt cas LEFT JOIN CasacApptTreatSelect cats
ON cas.iCasacApptID = cats.iCasacApptID
Hope this gives you the right idea.
Best of Luck
-Rice
-----Original Message-----
From: Amanda Stern [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 21, 2001 3:16 PM
To: CF-Talk
Subject: Cf in Select Stmt help
Hello,
I am trying to get this query to work and not sure
how...anyone have any ideas? The problem is in the
joining of the cas table and the cats table....there
is a 0 to many relationship between these tables....ie[cas.iCasacApptID =
cats.iCasacApptID]
many never equal each other because there may not be
an iCasacApptID in the cats table. If thats the case
then I want the word "None" as my cats.cTreatSelectID.
is there a way I can put a cfif statement around that
join..if so how..or is there a simplier way I am not
thinking of
Thanks again for the help!
<cfquery name="QRYtp"
datasource="#request.projectAceDb#">
<cfloop query="treatCount">
<cfif treatcount.TreatCount gt 6>
Select cas.iClientID ,
cd1.vCodeDecode as TreatSelect,
'#treatCount.TreatProg#' as TrueProg,
'#treatCount.HRACode#' as TrueHRA
>From CasacAppt cas,
CasacApptTreatSelect cats,
TreatmentProg tp,
CodeDecode cd1,
Client cl
Where cas.iClientID = cl.iClientID
AND '#treatCount.TreatProgID#' = cas.iTreatProgID
AND cas.iCasacApptID = cats.iCasacApptID
AND cats.cTreatSelectID = cd1.icodedecodeID
AND cas.dCasacDate >=
'#variables.stFieldNames.DBeginDate#'
AND cas.dCasacDate <=
'#variables.stFieldNames.dEndDate#'
AND cas.dCasacDate > '#request.dContractDate#'
</cfif>
</cfloop>
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
