SQL Functions in Where Clause

[Adam Michel]

I've been trying to use SQL functions such as YEAR() and MONTH() in a
where clause. Something like this:

<cfset dateList = model("date").findAll(where="YEAR(date) =
'#Year(Now())#'")>

I've tried a few variations as well with no luck using any of them. I
always get the following error back:
"Element at position [1] doesn't exist in array" from wheels\model
\crud.cfm: line 1232

It looks like Wheels is trying to parse the SQL function instead of
simply passing it through.

[Per Djurner]

That sort of SQL statement is not supported unfortunately.

You basically can't do anything "fancy" on the left hand side of the equals sign.

Some solutions for you:

- Rewrite the SQL to not use the YEAR function.

- Do a plain cfquery but put it in a method on the model to keep things clean.

- Look into calculated properties: http://cfwheels.org/docs/chapter/calculated-properties

/ Per

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To post to this group, send email to cfwh...@googlegroups.com.
To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.

[tpet...@gmail.com]

ticket opened

http://code.google.com/p/cfwheels/issues/detail?id=546

On Aug 14, 1:33 pm, Per Djurner <per.djur...@gmail.com> wrote:
> That sort of SQL statement is not supported unfortunately.
> You basically can't do anything "fancy" on the left hand side of the equals
> sign.
>
> Some solutions for you:
>
> - Rewrite the SQL to not use the YEAR function.
> - Do a plain cfquery but put it in a method on the model to keep things
> clean.
> - Look into calculated properties:http://cfwheels.org/docs/chapter/calculated-properties
>
> <http://cfwheels.org/docs/chapter/calculated-properties>/ Per
>

> On Sat, Aug 14, 2010 at 6:37 PM, Adam Michel <awmiche...@gmail.com> wrote:
> > I've been trying to use SQL functions such as YEAR() and MONTH() in a
> > where clause. Something like this:
>
> > <cfset dateList = model("date").findAll(where="YEAR(date) =
> > '#Year(Now())#'")>
>
> > I've tried a few variations as well with no luck using any of them. I
> > always get the following error back:
> > "Element at position [1] doesn't exist in array" from wheels\model
> > \crud.cfm: line 1232
>
> > It looks like Wheels is trying to parse the SQL function instead of
> > simply passing it through.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "ColdFusion on Wheels" group.
> > To post to this group, send email to cfwh...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > cfwheels+u...@googlegroups.com<cfwheels%2Bunsu...@googlegroups.com>
> > .