0

I've got one site in ColdFusion where the client now wants to get a list of certain users who are over a particular age. Here's my code so far:

<cfset minRefAge = 21 >
<cfquery name="rsReferees" datasource="nbsa">
SELECT ID, userFirstName + ' ' + userLastName AS refName, userTown, userDOB, userAccess
FROM UsersSSO
WHERE (dateDiff("yyyy", userDOB, now() ) => #minRefAge#) AND userAccess = 4
</cfquery>

userDOB is my date of birth field. When I run it, I get the following error:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

I can't spot the error. Could someone help?

Fish Below the Ice
  • 1,273
  • 13
  • 23
Joe Lowery
  • 562
  • 11
  • 26
  • what flavor of sql is this? – Russell Uhl Jun 06 '14 at 17:44
  • What is your DBMS? The correct syntax may be db dependent, so it saves time to always include that information with query questions. Side note: 1) Always use cfqueryparam to ensure proper handling and protect against sql injection and 2) Using functions in the `where` filter may hinder performance. See [What makes a SQL Statement Sargable](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable). – Leigh Jun 06 '14 at 17:52
  • 2
    I'm loathe to admit it, but it's Microsoft Access. Did I mention this was a legacy client? Should I get out the 10-foot pole? – Joe Lowery Jun 06 '14 at 18:44
  • 1
    It might be the greater than or equal sign mentioned by Russell. However, to isolate the problem, change your query to select * from UsersSSO where 1 = 2. Run it. Then build up the select clause one field at a time, and run it each time. If you haven't received an error, add userdob and useraccess to the select clause. If you still haven't thrown an error, add the datediff function. – Dan Bracuk Jun 06 '14 at 18:55
  • If it is Access, then your function syntax is correct. So Russell's probably right about the transposed operators. What happens when you run it directly in Access with a hard-coded value for `#minRefAge#`? – Leigh Jun 06 '14 at 19:18

1 Answers1

1

Looks like your greater than or equal to sign is backwards. Other than that, your query looks fine to me.

SELECT ID, userFirstName + ' ' + userLastName AS refName, userTown, userDOB, userAccess
FROM UsersSSO
WHERE (dateDiff("yyyy", userDOB, now() ) >= #minRefAge#) 
AND userAccess = 4

You may also need to put the ref age in quotes: '#minRefAge#'

Russell Uhl
  • 4,181
  • 2
  • 18
  • 28
  • 1
    *your query looks fine to me* There is not enough information yet. We need to know their dbms. Not all db's use quotes around the interval ie `yyyy` or have a `now()` function. – Leigh Jun 06 '14 at 18:00
  • @Leigh I agree. However, given the information we DO have, the general syntax is correct. There are no obvious spelling errors, there isn't a missing SELECT keyword, etc. – Russell Uhl Jun 06 '14 at 18:09
  • *the general syntax is correct* Not necessarily. If they are using MySQL, the function syntax way off. However, I honestly cannot tell which dbms they are using. Obviously something in the syntax IS wrong, but again, we do not yet have enough information to offer any useful advice on that score. – Leigh Jun 06 '14 at 18:40
  • Yep - it was the gte sign - plus an errant comma that snuck in there somehow. Thanks! – Joe Lowery Jun 06 '14 at 21:51