This is an urgent problem... thanks for any help offered.
I've got a query that retreives product names and quantities sold. Having
spent ages trying to get the query to return the recordset sorted by the
amount sold (desc), I've given up (I'm trying to output the top 5 items sold
on a website).
So far, this query returns the recordset...
<!--- Get the orders --->
<cfquery datasource="#attributes.DataSource#" name="GetOrders">
SELECT PurchaseItem.Name,
SUM(PurchaseItem.Quantity) AS AmountSold,
PurchaseStatus.Status
FROM (Purchase INNER JOIN PurchaseItem ON Purchase.PurchaseID =
PurchaseItem.PurchaseID)
LEFT JOIN PurchaseStatus ON PurchaseItem.PurchaseItemID =
PurchaseStatus.PurchaseItemID
WHERE ShopUID = '#Session.ShopUID#'
AND Status = 2
GROUP BY PurchaseItem.Name, PurchaseStatus.Status
ORDER BY SUM(PurchaseItem.Quantity), PurchaseItem.Name
</cfquery>
I figured I'd create a new query, sort it by the QuantitySold column using
ArraySort (Ben, CFWACK p920 says you can use Array functions on Queries)
like this:
<CFSET variables.Sales = QueryNew("Quantity, ProductName")>
<CFOUTPUT QUERY="GetOrders">
<CFSET variables.Result = QueryAddRow(variables.Sales)>
<CFSET variables.Result = QuerySetCell(variables.Sales, "Quantity",
AmountSold)>
<CFSET variables.Result = QuerySetCell(variables.Sales, "ProductName",
Name)>
</CFOUTPUT>
<CFSET variables.Sales = ArraySort(variables.Sales[Quantity], "numeric",
"desc")>
but that throws an error:
Error resolving parameter QUANTITY
The error occurred while evaluating the expression:
variables.Sales = ArraySort(variables.Sales[Quantity], "numeric", "desc")
Can anyone please help? Thanks if you can.
--
Aidan Whitehall <[EMAIL PROTECTED]>
Netshopper UK Ltd
Advanced Web Solutions & Services
http://www.netshopperuk.com/
Telephone +44 (01744) 648650
Fax +44 (01744) 648651
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
