I'm running the following Query of Query and getting an error:
Timestamp: Name of the column which is holding all date and time related values.
MyDatabase: Name of the database
Events: Name of another column with the name "Events" holding various values like, FIRST, SECOND,THIRD etc. I have mentioned FIRST here for convinience and clarity.
<cfquery datasource = "XX.XX.X.XX" name="master">
SELECT count(Timestamp) as COUNT,date_format(Timestamp,'%m-%d-%Y')
FROM MyDatabase
WHERE EVENTS = "FIRST" GROUP BY Timestamp ;
</cfquery>
<cfquery dbtype="query" name="detail">
SELECT *
FROM master
WHERE Timestamp > <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_date"> AND Timestamp < <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_date">;
</cfquery>
As far as the startdate and enddate parameters are concerned, I have set them as follows:
<cfparam name="form.startdate" default="#dateformat(now()-5, 'mm/dd/yyyy')#">
<cfparam name="form.enddate" default="#dateformat(now()-1, 'mm/dd/yyyy')#">
<cfparam name="form.selectdate" default="#dateformat(now(), 'mm/dd/yyyy')#">
I'm getting the folowign error:
Error Executing Database Query.
Query Of Queries syntax error.
Encountered "Timestamp. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,
The error occurred in line 40
38 : SELECT *
39 : FROM master
40 : WHERE Timestamp > <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_date"> AND Timestamp < <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_date">;
41 :
42 :
Could anyone tell me what went wrong?
Update:
After fixing the # sign, I am now getting the following error:
Error Executing Database Query.
Query Of Queries syntax error.
Encountered "Timestamp. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,
The error occurred in line 40
38 : SELECT *
39 : FROM master
40 : WHERE Timestamp > <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_date"> AND Timestamp < <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_date">;
41 :
42 :
value="#dateAdd('d', 1,form.enddate#)"
Your closing # sign is misplaced. It should be after the closing parenthesis:
value="#dateAdd('d', 1, form.enddate)#"
Update:
"Timestamp" and "Count" are bad choices for a column name or alias because they are reserved words in many databases. As Adrian mentioned in the comments, both are reserved words in CF QoQ's as well.. To use "Timestamp" in your QoQ, you must escape it by enclosing it in []. In the long term, you are better off renaming the columns and avoiding the issue altogether.
WHERE [Timestamp] >= <cfqueryparam ....>
AND [Timestamp] < <cfqueryparam ....>
As an aside, there is a slight bug in your SQL comparison. The end date operator should be <. By using <= the comparison includes an extra minute, so there is a slight chance it will pick up extra records ie dateAdd('d', 1, form.enddate) at midnight. The correct comparison is:
WHERE Timestamp >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_date">
AND Timestamp < <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_date">;