[cfaussie] Re: Date problems in Query of Queries

[Phil Rasmussen]

Actually Steve the Sum() part works fine suprisingly. But the group by does nothing, and it would make sense that the columns in a QoQ don't have data types...so I guess Dates mean nothing in a QoQ...which really sux because I really need to limit the query to a date range.   Thanks mate,   Phil "Steve Onnis" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... 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 >= &apos;{ts &apos;2003-02-01 00:00:00&apos;}&apos; 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/