I recomend NEVER using the ORDER BY
with the column number syntax.
It is easy to change the SELECT clause or
have someone else come in and change it
and your ORDER BY is then completely
wrong. The GROUP BY would then also
be broken. I ALWAYS reference columns by
their name. ;)
Best regards
-Rice
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 23, 2001 1:12 PM
To: CF-Talk
Subject: RE: SQL help/group in CFOUTPUT not working??????
I thought that when you were using an ORDER BY with a Union Select you used
the column numbers instead of names, so adding
Order By 3
to the bottom of the query should work
HTH
Hatton Humphrey
> -----Original Message-----
> From: Rice, John J [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, August 23, 2001 1:00 PM
> To: CF-Talk
> Subject: RE: SQL help/group in CFOUTPUT not working??????
>
>
>
> You may want to append an
>
> ORDER BY iCasacApptID
>
> to the second query in your UNION.
>
>
>
> -----Original Message-----
> From: Amanda Stern [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, August 23, 2001 11:34 AM
> To: CF-Talk
> Subject: SQL help/group in CFOUTPUT not working??????
>
>
> hello, I've been working on this query for a while and
> cant get it to work the way I need it to, i am close
> though.....here is the problem - the cas table is
> keyed by [iCasacApptID], the cats table has a
> composite key of [iCasacApptID, cTreatSelectID]. So
> when I output the results if iCasacApptID is found in
> the cats table twice, then two records are outputed in
> my report instead of one....so, if there are 2
> TreatSelects to one CasacApptID, then I want that to
> be one row..I though that
> <table>
> <cfoutput>
> <tr>
> <td>#QRYtp.TreatSelect#</td>
> </tr>
> </cfoutput>
> </table>
>
> would accomplish this....but it isnt. here is my code
> below....can anyone see what I am missing...
>
> Thanks yet again!
>
>
>
>
> <cfloop query="treatCount">
>
> <cfif treatcount.TreatCount gt 6>
> <cfquery name="QRYtp" datatasource="#DB#">
>
> Select cas.iClientID ,
> cd1.vCodeDecode as TreatSelect,
> cas.iCasacApptID,
> '#treatCount.TreatProgID#' as TrueProgID,
> '#treatCount.TreatProg#' as TrueProg,
> '#treatCount.HRACode#' as TrueHRA
> From CasacAppt cas,
> CasacApptTreatSelect cats,
> CodeDecode cd1
> Where '#treatCount.TreatProgID#' = cas.iTreatProgID
> and cas.iCasacApptID = cats.iCasacApptID
> AND cats.cTreatSelectID = cd1.icodedecodeID
> AND cas.dCasacDate = '#variables.DBeginDate#'
> AND cas.dCasacDate <= '#variables.dEndDate#'
> AND cas.iCasacApptID in (select cs.icasacApptID
> from CasacApptTreatSelect cs)
>
> Union
>
> Select cas.iClientID ,
> 'None' as TreatSelect,
> cas.iCasacApptID,
> '#treatCount.TreatProgID#' as TrueProgID,
> '#treatCount.TreatProg#' as TrueProg,
> '#treatCount.HRACode#' as TrueHRA
> From CasacAppt cas,
> CodeDecode cd1,
> Client cl
> Where cas.iClientID = cl.iClientID
> AND '#treatCount.TreatProgID#' = cas.iTreatProgID
>
> AND cas.dCasacDate >= '#variables.DBeginDate#'
> AND cas.dCasacDate <= '#variables.dEndDate#'
> AND cas.iCasacApptID not in (select
> cs.icasacApptID from CasacApptTreatSelect cs)
>
> </cfquery>
>
>
> <table align="center" border="1" width="650">
> <tr>
> <cfoutput query="QRYtp" group="iCasacApptID">
> <td>#QRYtp.iCasacApptID#</td>
> <td>#QRYtp.TrueProg#</td>
> <td>#QRYtp.iClientID#</td>
> <TD>
> <table>
> <cfoutput>
> <tr>
> <td>#QRYtp.TreatSelect#</td>
> </tr>
> </cfoutput>
> </table>
> </TD>
> </tr></cfoutput>
>
> </table></cfif>
>
>
> </cfloop>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
