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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
