on 6/12/03 4:16 PM, Scott Weikert at [EMAIL PROTECTED] wrote:
>> Is there some way to calculate #DistinctPropertyCount# some way from the
>> results of the query? In this case, the RecordCount is correct (10 rows
>> returned) but what I *really* need is the number of *properties* that are
>> returned...
>
> You could create a structure, with keys being the value of the property
> field that is duped in your query, starting with values of 0. Loop the
> query, and increment the structure key's value by one each time you hit it...
>
> Or, do a second query with the same criteria, but just query for that one
> field and do a DISTINCT on it, then your recordcount for that query should
> be the number of distinct values for that field.
Man, I totally tried this, but my query just gave the most horrible
errors...
Watch...
<cfif IsDefined("URL.Bedrooms")>
<cfquery name="getMyListings" datasource="#Application.DSN#">
SELECT *
FROM (tblRentalLocations INNER JOIN tblRentalProperties ON
tblRentalLocations.LocationID=tblRentalProperties.frn_LocationID)
INNER JOIN tblRentalRates ON
tblRentalProperties.PropertyID=tblRentalRates.frn_PropertyID
<cfif URL.Bedrooms NEQ 'ANY'>
WHERE tblRentalProperties.PropertyBedrooms = #URL.Bedrooms#
</cfif>
ORDER BY tblRentalProperties.PropertyName, tblRentalRates.RatesOrder,
tblRentalRates.RatesLength Desc
</cfquery>
</cfif>
Now...right below it, I make the EXACT same bit of code, and simply request
the count, not even the distinct count...and I get the error below:
<cfif IsDefined("URL.Bedrooms")>
<cfquery name="getMyListings" datasource="#Application.DSN#">
SELECT COUNT(PropertyID) AS PropertyCount
FROM (tblRentalLocations INNER JOIN tblRentalProperties ON
tblRentalLocations.LocationID=tblRentalProperties.frn_LocationID)
INNER JOIN tblRentalRates ON
tblRentalProperties.PropertyID=tblRentalRates.frn_PropertyID
<cfif URL.Bedrooms NEQ 'ANY'>
WHERE tblRentalProperties.PropertyBedrooms = #URL.Bedrooms#
</cfif>
ORDER BY tblRentalProperties.PropertyName, tblRentalRates.RatesOrder,
tblRentalRates.RatesLength Desc
</cfquery>
</cfif>
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access
Driver] You tried to execute a query that does not include the specified
expression
'tblRentalProperties.PropertyName' as part of an aggregate function.
I've tried to look up help on this particular error, but I'm stumped, so I
assumed it wasn't going to work because I couldn't perform the *exact* same
query in both places...just returning different data...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
