Incorrect syntax near '=' error that I can't solve

Rich85 — Thu, 05 Sep 2024 08:02:20 GMT

Hi,

I'm receiving the error Incorrect syntax near '=' when I run simple queries like the example below. This only happens when I use a column created using a CASE statement in the WHERE clause. I can use any other column in the WHERE clause, including the original columns used to create the new column, and the query runs fine.

I'm sure this is a really simple one but it's escaping me at the minute. Any help would be greatly appreciated

WITH isos AS

(

SELECT DISTINCT

Name,

CAST(RecordingTime AS DATE) AS testDate,

LOWER(TestType) AS testType,

CASE

WHEN LOWER(TestTypeName) = 'slppf' OR LOWER(TestType) = 'rsaip' THEN 'ankle iso push'

WHEN LOWER(TestTypeName) = 'slsquat' OR LOWER(TestType) = 'rskip' THEN 'knee iso push'

WHEN LOWER(TestTypeName) = 'slhipext' OR LOWER(TestType) = 'rship' THEN 'hip iso push'

ELSE LOWER(TestTypeName)

END AS testTypeName,

`Peak Vertical Force / BW` AS peakVerticalForce

FROM

my_table

)

SELECT *

FROM isos

WHERE testTypeName = 'hip iso push'

Re: Incorrect syntax near '=' error that I can't solve

Kayla — Thu, 05 Sep 2024 11:57:23 GMT

What jumps out to me at first is the backticks on `Peak Vertical Force / BW`, but I'm assuming that's just a column name and not an attempt at division.

Next that jumps out is TestType and TestTypeName being aliased as testType and testTypeName- spark by default is case insensitive with column names, which could be contributing to issues.

I don't see anything else that jumps out to me as fundamentally wrong, so next step is to just start tweaking/removing random things until it works and then re-adding to see where the line between broken/working is.

I'd start by removing the WHERE clause and seeing what shows up, and if that doesn't work strip back the columns selected and turn the WHEN...OR...THEN into a separate WHEN for each condition.

topic Incorrect syntax near '=' error that I can't solve in Warehousing & Analytics

Incorrect syntax near '=' error that I can't solve

Re: Incorrect syntax near '=' error that I can't solve