Ok, I'm stuck on something again. I'm querying a table that contains
records of site views. Very simple, it collects the record_id of the
tracked site, the datetime, and ip address everytime the page is
viewed.
I want to query it for all hits for a specific page within the past 60
minutes. I subtract 60 minutes from the "NOW" variable and set it
equal to 'sixty_previous'. I then use both NOW and SIXTY_PREVIOUS to
do a 'between' operation that fetches me records that are no more than
an hour old.
<CFQUERY datasource="dns" name="last_hour">
Select hit_id, datetime, dns_id
FROM tbl_dns_hits
WHERE (dns_id = '#dns_lookup.dns_id#') and (dns_hit_datetime between
#sixty_previous# and #now()#)
</CFQUERY>
This works, and returns the data that I want. Now that I have a set of
records that represent the past hour of site views, I then want to
break that query down into 12 smaller pieces. These would be queries
representing 5 minute intervals within that hour. I figured it would
be more effecient to query the previous query, since I knew that it
contained all the possible records I would use, it is already in
memory, and it prevents unnecessary calls to the actual table.
However, I think I've discovered an issue with query of queries not
recognizing dates correctly. Look at the sytax for the query above.
specifically this part
WHERE (dns_id = '#dns_lookup.dns_id#') and (dns_hit_datetime between
#sixty_previous# and #now()#)
THIS WORKS. It returns what I need. However, if I build the following
query using the previous query as the source, I get an error. Here is
the problem code.
<CFQUERY dbtype="query" name="get_five_mins">
Select hit_id
FROM last_hour <!--- the previous query
WHERE (dns_id = '#dns_lookup.dns_id#') and (datetime between
#five_previous# and #now()#)
</CFQUERY>
<!---- What breaks it. If I remove this code, the query runs--->
<!---- and (dns_hit_datetime between #five_previous# and #now()#) --->
the only real difference is the use of the different variables
#five_previous#
#sixty_previous#
But I have verified that both variables are using dates with the same
formatting. So the syntax to compare dates that works on a traditional
query does not work when you are comparing dates from a query within a
query. This code below works when used in a traditional query, but
generates an exception when used in a query of a query.
(datetime between {ts '2010-02-09 19:30:03'} and {ts '2010-02-09
20:30:03'})
Here is the exception (Expression Error) when syntax used in query of
query
Extended Info -
Select hit_id FROM last_hour WHERE (dns_id = '206') and (datetime
between {ts '2010-02-09 20:51:08'} and {ts '2010-02-09 20:56:08'})
Detail -
The following error was produced: Encountered "dns_hit_datetime
between {" at line 5, column 37. Was expecting one of: "not" ...
"(" ... "?" ... <STRING_LITERAL> ... "[" ... <IDENTIFIER> "." ...
<IDENTIFIER> "+" ... <IDENTIFIER> ")" ... <INTEGER_LITERAL> ...
<FLOATING_POINT_LITERAL> ... "count" ... "avg" ... "sum" ... "min" ...
"max" ... <IDENTIFIER> "*" ... <IDENTIFIER> "/" ... <IDENTIFIER>
"-" ... <IDENTIFIER> "not" ... <IDENTIFIER> "like" ... <IDENTIFIER>
"<=" ... <IDENTIFIER> ">=" ... <IDENTIFIER> "<>" ... <IDENTIFIER> "!
=" ... <IDENTIFIER> "=" ... <IDENTIFIER> "<" ... <IDENTIFIER> ">" ...
<IDENTIFIER> "is" ... <IDENTIFIER> "between" <INTEGER_LITERAL> ...
<IDENTIFIER> "between" <FLOATING_POINT_LITERAL> ... <IDENTIFIER>
"between" "count" ... <IDENTIFIER> "between" "avg" ... <IDENTIFIER>
"between" "sum" ... <IDENTIFIER> "between" "min" ... <IDENTIFIER>
"between" "max" ... <IDENTIFIER> "between" "[" ... <IDENTIFIER>
"between" <IDENTIFIER> ... <IDENTIFIER> "between" "(" ... <IDENTIFIER>
"between" "+" ... <IDENTIFIER> "between" "-" ... <IDENTIFIER>
"between" "?" ... <IDENTIFIER> "between" <STRING_LITERAL> ...
<IDENTIFIER> "between" "upper" ... <IDENTIFIER> "between" "lower" ...
<IDENTIFIER> "in" ...
--
Open BlueDragon Public Mailing List
http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon
mailing list - http://groups.google.com/group/openbd?hl=en
!! save a network - please trim replies before posting !!
