Hi. I'm having a very annoying problem with CFMX, witch i as not having with cf5. I
have a query that i use to generate a report page, with all sales by month. So i have,
for exemple, on 2002-12 (december 2002) X sales of product Y.
I then use a query of query to sort and filter the data i want. In CFMX, when i try to
get the field
here is the code (dont mind to understand it):
<CFQUERY NAME="qGetData" DATASOURCE="#DSN_AOL#">
SELECT orders.OrderID, products.SupplierID, products.ProductID,
products.ProductName, orders.OrderDate, DATE_FORMAT(orders.OrderDate,'%Y-%m') AS Data,
MONTH(orders.OrderDate) as Mes, YEAR(orders.OrderDate) as Ano, orders.Payed,
orders.Desconsiderar, SUM(orderitemskit.itemquantity) as quantity
FROM orders, orderitemskit, products
WHERE orders.OrderID = orderitemskit.OrderID AND
orderitemskit.ItemProductID = products.ProductID AND
(products.SupplierID =2) AND (orders.Payed =1) AND
(orders.Desconsiderar <>1)
GROUP by products.ProductID,Data
UNION ALL
SELECT orders.OrderID, products.SupplierID, products.ProductID,
products.ProductName, orders.OrderDate, DATE_FORMAT(orders.OrderDate,'%Y-%m') AS Data,
MONTH(orders.OrderDate) as Mes, YEAR(orders.OrderDate) as Ano, orders.Payed,
orders.Desconsiderar, SUM(orderitems.quantity) as quantity
FROM orders, orderitems, products
WHERE orders.OrderID = orderitems.OrderID AND
orderitems.ProductID = products.ProductID AND
(products.SupplierID =2) AND (orders.Payed =1) AND
(orders.Desconsiderar <>1)
GROUP by products.ProductID,Data
ORDER BY products.ProductID, Data ASC
</CFQUERY>
<cfquery name="qGetFilteredData" dbtype="query">
SELECT orderdate, supplierID, orderid, productname, quantity, payed, Desconsiderar,
ProductID, Data, Ano, Mes
FROM qGetData
</cfquery>
======================
note the UNION clause.
The problem seems to be in the "DATE_FORMAT(orders.OrderDate,'%Y-%m') AS Data"
expression. When i try to get the "Data" column of the query, using query of query,
CFMX throwns the following:
--------------------------
Error casting an object of type [B to an incompatible type. This usually indicates a
programming error in Java, although it could also mean you have tried to use a foreign
object in a different way than it was designed. [B
--------------------
I have never heard of this error. I even dont know what it means. I used to have this
problem when outputting this kind of column, using cfoutput. I solved this by using
the TOSTRING() function (ie: <cfoutput>#ToString(Data)#</cfoutput). But this is
something that i cant to in query of query body, so i'm totaly stuck.
I need the query of query, i cant change the data type in the master query and i cant
use ToString function.
Can you give me a hand or point me in a direction? Let me know if i didnt make myself
clear.
Thank you very much.
Leonardo.
Tip: if you're having the same problem with outputting, use the ToString function. It
worked for me.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
