Copy link to clipboard
Copied
Once again I'm stuck! I have included the query I'm trying to run below. I have also indicated the data types of my fields.
<cfquery name="qGetOrders" datasource="manna_premier">
SELECT DISTINCT Count(ID) AS CountOfID, - number
TMName, -text
Sum(Quantity) AS SumOfQuantity, - number
Count(NewExisting) AS CountOfNewExisting -number
FROM Users, Orders, ProductOrders
WHERE SaleDate BETWEEN #CreateODBCDate(FORM.Start)# AND #CreateODBCDate(FORM.End)#
GROUP BY UserZone, TMName
</cfquery>
When run it returns this error message....
Error Executing Database Query. | ||||||||
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. | ||||||||
The error occurred in D:\Inetpub\mannapremier\zvp_report2.cfm: line 11 | ||||||||
9 : Count(NewExisting) AS CountOfNewExisting 10 : FROM Users, Orders, ProductOrders 11 : WHERE SaleDate BETWEEN #CreateODBCDate(FORM.Start)# AND #CreateODBCDate(FORM.End)# 12 : GROUP BY UserZone, TMName 13 : </cfquery> | ||||||||
|
I use the where clause on several other pages and it works. I don't understand where my data type is mismatched.
Can anyone point me in the right direction?
Copy link to clipboard
Copied
Look into the following:
1) The columns that appear in the group-by clause(UserZone, TMName) must also appear in the select-clause
2) Is the datatype of the column SaleDate actually a date?
3) Do you really need to select from the 3 tables Users, Orders, ProductOrders? It is apparent to me you don't. If not, then select from just the table(s) in which the columns occur.
4) If you indeed need to select from the 3 tables Users, Orders, ProductOrders, then you should qualify the column names like this, Users.ID, productOrders.Quantity, and so on.
Copy link to clipboard
Copied
4) If you indeed need to select from the 3 tables Users,
Orders, ProductOrders, then you should qualify the columns
names like this, Users.ID, productOrders.Quantity, and so
on.
You also need to join on the related columns. If you forget to do that the resultset will be a massive cartesian table (huge number of records):
rows in Table1 * #rows in Table2 * #rows in Table3
Copy link to clipboard
Copied
In addition to what's been said so far, this:
#CreateODBCDate(FORM.Start)#
will cause problems if it's not already causing problems.
First, use parsedatetime() to convert the strings from your form fields to proper date objects.
Second, use cfqueryparam. It will help.