Hi all,
I have two pratically identical SQL statements. The only difference is this
line:
AND RP.productID = <cfqueryparam value="#URL.productID#"
cfsqltype="cf_sql_integer" />
When this line is included the output of the decimal field RP.options changes
from 2.50 to 2
Here are the two statements:
==========
Statement 1
==========
<cfquery name="getAll" datasource="#theDatabase#">
SELECT RP.productID
,RP.productTitle
,RP.bookingcode
,RP.priority
,RP.options
,count(RI.itemTitle) as numbItems
,RI.itemID
,RI.itemTitle
,RI.bookingcode as bookingcode2
,RI.itemstartdate
,RI.itemenddate
FROM reg_products as RP
LEFT OUTER JOIN
reg_products_list as RPL
ON RP.productID = RPL.productID
AND RP.productID = <cfqueryparam value="#URL.productID#"
cfsqltype="cf_sql_integer" />
LEFT OUTER JOIN
reg_items as RI
ON RPL.itemID = RI.itemID
WHERE RP.active = '1'
AND RP.eventID = '#theEventID#'
AND RP.lingo = '#theLingo#'
GROUP BY RP.productID
,RI.itemID
,RP.productTitle
,RP.bookingcode
,RP.priority
,RI.itemTitle
,RI.bookingcode
,RI.itemstartdate
,RI.itemenddate
,RP.options
ORDER BY RP.priority
</cfquery>
==========
Statement 2
==========
<cfquery name="getAll" datasource="#theDatabase#">
SELECT RP.productID
,RP.productTitle
,RP.bookingcode
,RP.priority
,RP.options
,count(RI.itemTitle) as numbItems
,RI.itemID
,RI.itemTitle
,RI.bookingcode as bookingcode2
,RI.itemstartdate
,RI.itemenddate
FROM reg_products as RP
LEFT OUTER JOIN
reg_products_list as RPL
ON RP.productID = RPL.productID
LEFT OUTER JOIN
reg_items as RI
ON RPL.itemID = RI.itemID
WHERE RP.active = '1'
AND RP.eventID = '#theEventID#'
AND RP.lingo = '#theLingo#'
GROUP BY RP.productID
,RP.productTitle
,RI.itemID
,RP.bookingcode
,RP.priority
,RI.itemTitle
,RI.bookingcode
,RI.itemstartdate
,RI.itemenddate
,RP.options
ORDER BY RP.priority
</cfquery>
Any ideas how to fix this problem?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Macromedia ColdFusion MX7
Upgrade to MX7 & experience time-saving features, more productivity.
http://www.adobe.com/products/coldfusion?sdid=RVJW
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275935
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
