Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

ODBC SQL incorrect syntax near '='

Hello,

 

I am trying to run this SQL using ODBC connection in Power Query:

 

SELECT
IIF(column1 = 'C', Left(column2,10), '') AS 'Column3'
FROM table

 

 

But keep getting the following error:

DataSource.Error: ODBC: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '='.

 

What do I need to do to correct this?

 

Thank you in advance.

 

Vivek

8 REPLIES 8
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

Based on my test, it could work in my side so the syntax seems correct.

sql.png

Please try to change the table name as a whole table name path like database.tablename to check it or clear the permissions to try to reconnect to ODBC.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

negi007
Community Champion
Community Champion

@Anonymous your sql syntext seems to be correct. You can test your sql code at below

https://www.eversql.com/sql-syntax-check-validator/

 

if you have access to query browser of the DB, you may try running query there to check if it is working fine at DB end or not. 

 

SELECT
IIF(table1.column1 = 'C', Left(table1.column2, 10), '') AS 'Column3'
FROM
table1

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

Watsky
Solution Sage
Solution Sage

Hey @Anonymous 

 

Usually when I see this error it is due to the single quotes for the declaration try removing the single quotes on Column3.

 

You can also try adding double quotes, however if you do this in Power Query via Advanced Editor then you'll need double quotes x2 like  ""Column3"".


Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up!
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

Proud to be a Super User!

Anonymous
Not applicable

Hi,

 

"," in the end of the statement could be the cause of error. Try this

 

SELECT
IIF(column1 = 'C', Left(column2,10), '') AS 'Column3'
FROM table

 

Thanks!

Anonymous
Not applicable

Hi @Anonymous ,

 

Thanks for the help. I removed the "," in the end and still get the same error.  The error shows for something with the "=".

 

Vivek

Anonymous
Not applicable

Well in that case, what's the datatype of Column1?

Anonymous
Not applicable

@AnonymousThe datatype is text for Column1 

Anonymous
Not applicable

hi @Anonymous 

 

Try this. I believe the problem is not with "=", but the condition expression. Also I hope the datatype of Column2 is also varchar, else it could be problem too.

 

SELECT
IIF(cast(column1 as varchar(10) = 'C', Left(column2,10), '') AS 'Column3'
FROM table

 

Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.