0
<cfquery name="findQtrs" dbtype="query">
	SELECT DISTINCT DATEADD(qq, DATEDIFF(qq, 0, event_datestart), 0) as SQtr, DATEADD(qq, DATEDIFF(qq, -1, event_datestart), -1)as EQtr
	FROM eventData
	ORDER BY SQtr ASC
</cfquery>

when I run the above I get error in syntax of '(' looking for a from. However, when I run that exact query in SQL Server, I get the results I want and need. Does a query have to be set up differently when it is a query of query?

Thanks!

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

5
Contributors
5
Replies
6
Views
7 Years
Discussion Span
Last Post by arrgh
0

Not all of MS SQL's functions are available in a Query of Query. I'm guessing it just doesn't recognize the DateAdd or DateDiff functions.

Maybe try the MS SQL equivalent of a View?

0

Does a query have to be set up differently when it is a query of query?

Yes.

You can't use regular CF functions inside a QoQ. It's not a database query, so it's internal syntax is very limited. Only a handful of functions are implemented. None of which will help in your case.

If you need that functionality you'll have to 1) use date functions it in the original database query or 2) loop through the query, record by record, and use CF's date functions on the values (obviously a bad option).

0

As a general rule, I would leave as much data crunching work to be done as possible on the database server as it will be much more efficient.

In the situation you described, it might be better to create a "View" in the database based on your above SQL and then use a simple query to pull data out of that.

0

As a general rule, I would leave as much data crunching work to be done as possible on the database server as it will be much more efficient.

Like I said, in this case they have almost no choice. Since it can't be done in a QoQ ;-)

In the situation you described, it might be better to create a "View" in the database based on your above SQL and then use a simple query to pull data out of that.

Maybe. Views tend to be useful when the same calculated information is used in multiple places. If something is only used on once, or one page only, it's generally not worth it to create a view just for that purpose.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.