Incorrect syntax near the keyword 'FROM'

2897
7
10-01-2015 02:06 PM
ChrisPolley
New Contributor III

i'm having an issues here, I'm trying to Select by attributes and using the following

(EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM "DateReported") < 10) AND "Facility" = 'Main' AND  ("Cause" = 'Other' OR "DateRepaired" IS NULL)

and I'm getting the following error

Incorrect syntax near the keywrod 'FROM'

0 Kudos
7 Replies
JenniferMcCall4
Occasional Contributor III

Have you tried breaking up the statement into smaller individual statements to see which section has the error?

ChrisPolley
New Contributor III

Yes and no

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

It seems EXTRACT is working and CURRENT_DATE, is the issue with the DateReported field?  Does this work?

  • (EXTRACT(YEAR FROM "DateReported"))
0 Kudos
DarrenWiens2
MVP Honored Contributor

Can you get it to work for a simpler expression (e.g. "EXTRACT(YEAR FROM "DateReported")=2010")?

JoshuaBixby
MVP Esteemed Contributor

What about moving the right-parenthesis over to before the less than comparison:

(EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM "DateReported")) < 10 AND "Facility" = 'Main' AND ("Cause" = 'Other' OR "DateRepaired" IS NULL)

ChrisPolley
New Contributor III

Tried that and I'm getting the same error.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

do the following and tell if it works

  • (EXTRACT(YEAR FROM CURRENT_DATE))      - note the brackets!
  • ((EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM "DateReported"))  < 10 )    - ditto!!

If either fails, let us know which one... build the steps incrementally and manually watching that you may need to add brackets as I have done in the above 2 examples

0 Kudos