Skip to main content
Solved

Case statement in SQL Creator Update Query

  • October 22, 2013
  • 4 replies
  • 81 views
E
I'm trying to update a value in a field to a new value based on a case statement. I'm using SQL Creator to do this.

 

 

I can update a field just fine with Update [Table] Set [Field] = "PASS".

 

But if I  try to set the field equal to a case statement I get an error that says "Provider error `(-2147217900) Syntax error (missing operator) in query expression '(case when".

 

 

Here is the exact query I'm using:

 

Update [TableName] set [Sector Lat Valid] = case when [RBS Lat] is null then "NA" when [RBS Lat]>=0 and [RBS Long]<=90 then "PASS" else "FAIL" end

 

 

Does anyone have an idea what I'm doing wrong? Thanks in advance?

Best answer by david_r

Hi,

 

 

the syntax of the SQLCreator / SQLExecutor is not specific for FME, it follows the underlying database. So the syntax may be different between Oracle and SQL Server, for example.

 

 

Since you're referencing table names using brackes "[ ... ]", I'm suspecting that you're using MS Access? If that is so, Access does not support CASE statements, you will have to translate into an IIF function. See here for a discussion with examples.

 

 

David
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

4 replies

takashi
Celebrity
  • takashiCelebrity
  • October 22, 2013
Hi,

 

 

Try using single quotations '<value>' instead of double quotations "<value>".

 

 

Takashi
Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
E
  • erin
  • Author
  • October 23, 2013
Unfortunately I get the same error whether I use single or double quotations.
david_r
Celebrity
  • david_rCelebrity
  • Best Answer
  • October 23, 2013
Hi,

 

 

the syntax of the SQLCreator / SQLExecutor is not specific for FME, it follows the underlying database. So the syntax may be different between Oracle and SQL Server, for example.

 

 

Since you're referencing table names using brackes "[ ... ]", I'm suspecting that you're using MS Access? If that is so, Access does not support CASE statements, you will have to translate into an IIF function. See here for a discussion with examples.

 

 

David
E
  • erin
  • Author
  • October 23, 2013
Perfect! Worked like a charm! Thanks!

Community Stats

32,154
Posts
122,043
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.