Search code examples
mysqldatecoldfusioncfquery

Coldfusion MySQL Query within a Query

I am trying to retrieve records that falls in specific year and month from a full set of records retrieve for a particular user. Firstly, the field in question itemdate is MySql type Datetime (e.g. 2016-08-15 20:00:25). There are 4 rows of records for a user:

2016-08-15 20:00:25
2015-06-01 20:25:05
2016-08-15 20:26:00
2016-08-15 23:30:35

These records for a particular user are retrieved by:

<cfquery datasource="userdatbase"name="reportlist">
select itemid, itemdate, itemvalue
from itemlib
where userid = '#currentuserid#'
</cfquery>

The current year and month is determined by:

<cfset thisyear = #Year(Now())#>
<cfset thismonth = #Month(Now())#>

Now filter the original query and get only those from current year and month:

    <cfquery dbtype="query" name="detail"> 
        select itemid, itemdate, itemvalue
        from reportlist
        where year(itemdate) = #thisyear#
        and month(itemdate) = #thismonth#
    </cfquery>

I am getting the following error:

Error Executing Database Query.

Query Of Queries syntax error. Encountered "year. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,

I have tried to do this instead:

    <cfquery dbtype="query" name="detail"> 
        select itemid, itemdate, itemvalue
        from reportlist
        where #year(reportlist.itemdate)# = #thisyear#
        and #month(reportlist.itemdate)# = #thismonth#
    </cfquery>

which will get rid of the error. However, I got all 4 rows of records in the detail query instead of the expected 3. I do not expect the 2015-06-01 20:25:05 record to be selected, but it does. I am scratching my head over this. Any help is appreciated. Thank you in advance.

Solution

  • A coldfusion Query of Queries (QofQ) does not have the full functionality of a dbms. year() is not supported in QofQ syntax.

    Your second query is syntactically correct because it is passing in the value of the reportlist.itemdate, which will be the value of itemdate in the first row of the query reportlist (assuming you are not looping over reportlist). ColdFusion interprets this as comparing two literal values instead of comparing the row values in the query.

    Alternatively you could set a variable (fromDate) to the first date of the month you want, then filter your query where the item date is >= the fromDate and < fromDate plus 1 month. See below:

    <!--- set fromDate to the first date of the month --->
<cfset fromDate = createDate(thisyear, thismonth, 1) />
<cfquery dbtype="query" name="detail"> 
    select itemid, itemdate, itemvalue
    from reportlist
    where itemdate >= <cfqueryparam value="#fromDate#" cfsqltype="cf_sql_date" />
          and itemdate < <cfqueryparam value="#dateAdd('m', 1, fromDate)#" cfsqltype="cf_sql_date" />
</cfquery>

    This accomplishes the same thing by using comparators supported in a ColdFusion QoQ.

    Some helpful documentation here: http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html