[cfaussie] RE: Date problems in Query of Queries

Steve Onnis Mon, 03 Mar 2003 21:14:32 -0800

Phil

From what I recall, there is no such thing as column types in QofQ. Once you start to query a query, all columns are strings and integers.

Also, I dont think you can "group by" with query of query either, and am almost certain you cant use aggregated functions like SUM, COUNT and so on.

Regards

Steve Onnis
Domain Concept Designs
+61 422 337 685
+61 3 9431 4249

http://www.domainconceptdesigns.com [EMAIL PROTECTED]		http://www.cfcentral.com.au [EMAIL PROTECTED]

("If you think it can't be done, you haven't asked me!") - Steve Onnis

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Phil Rasmussen
Sent: Tuesday, March 04, 2003 4:01 PM
To: CFAussie Mailing List
Subject: [cfaussie] Date problems in Query of Queries
Hey all, just wondering if anyone has experienced problems using DateStamps in Query of Queries......in WHERE clauses specifically. All I am trying to do is a simple WHERE Date BETWEEN #DateFrom# AND #DateTo#   but I keep getting syntax errors. Both date variables are created using CreateODBCDateTime() and the query works perfectly ok directly off the database....but as soon as I run it as a query of queries, I get the syntax error. It makes no sense. I did read a few posts about possible bugs in CFMX Query of Queries implementation. Query and Error is below:

<cfset dateFrom = CreateODBCDateTime(CreateDate(2003, 02, 01))>
<cfset dateTo = DateAdd('D', 7, dateFrom)>

<cfquery name="NewBranchDetails" dbtype="query">
   SELECT       Transaction_Date, Sum(Value_Price) as TotalSales
   FROM          tempQuery
   WHERE      Transaction_DATE >= #DateFrom# AND <= #DateTo#
   GROUP BY   Transaction_Date
   ORDER BY   Transaction_Date asc

</cfquery>

The error message I get is:

SELECT Transaction_Date, Sum(Value_Price) as TotalSales FROM tempQuery WHERE Transaction_DATE >= '{ts '2003-02-01 00:00:00'}' GROUP BY

Query Of Queries syntax error.
Encountered "2003" at line 4, column 48.

The error occurred in C:\Inetpub\wwwroot\Graphing\index.cfm: line 116
114 : 	  GROUP BY   Transaction_Date
115 : 	  ORDER BY   Transaction_Date asc
116 : 	</cfquery> 
117 : 
118 : 	
Thanks,

Phil
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/

---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/

Hey all, just wondering if anyone has experienced problems using DateStamps in Query of Queries......in WHERE clauses specifically. All I am trying to do is a simple WHERE Date BETWEEN #DateFrom# AND #DateTo# but I keep getting syntax errors. Both date variables are created using CreateODBCDateTime() and the query works perfectly ok directly off the database....but as soon as I run it as a query of queries, I get the syntax error. It makes no sense. I did read a few posts about possible bugs in CFMX Query of Queries implementation. Query and Error is below: <cfset dateFrom = CreateODBCDateTime(CreateDate(2003, 02, 01))> <cfset dateTo = DateAdd('D', 7, dateFrom)> <cfquery name="NewBranchDetails" dbtype="query"> SELECT Transaction_Date, Sum(Value_Price) as TotalSales FROM tempQuery WHERE Transaction_DATE >= #DateFrom# AND <= #DateTo# GROUP BY Transaction_Date ORDER BY Transaction_Date asc </cfquery> The error message I get is: *SELECT Transaction_Date, Sum(Value_Price) as TotalSales FROM tempQuery WHERE Transaction_DATE >= '{ts '2003-02-01 00:00:00'}' GROUP BY* Query Of Queries syntax error. Encountered "2003" at line 4, column 48.

*The error occurred in C:\Inetpub\wwwroot\Graphing\index.cfm: line 116*
*114 : GROUP BY Transaction_Date 115 : ORDER BY Transaction_Date asc 116 : </cfquery> 117 : 118 :*


Thanks, Phil

[cfaussie] RE: Date problems in Query of Queries

Reply via email to