Syntax Error While Using Case Statement in Spark SQL

Question

I want to run a case statement that runs different SELECT Statements Based on a condition in Spark SQL but not able to get the Syntax Right .

My SQL statement looks like this

registerTable(sql="SELECT CASE WHEN typedKeyword > '' THEN (SELECT * FROM `temp.sdf0` WHERE  originalKeyword > ''AND  keyword > '' AND deviceType = 'devicetype' ) ELSE (SELECT * FROM `temp.tes` WHERE  originalKeyword > ''AND  keyword > '' ) END  ",alias="temp.test")

I don't know if CASE statement is supported in spark SQL so How can one achieve this

You should handle that in your logic and execute 2 different queries depending on the input — juergen d
– juergen d, Commented Sep 28, 2016 at 11:03
Or the best way for handling this is to create a Stored Proc as handling 2 different queries in not appreciated in SQL. — Ankit Bajpai
– Ankit Bajpai, Commented Sep 28, 2016 at 11:25

Guy Boo · Accepted Answer · 2016-09-28 11:32:31Z

I've worked in nearly a dozen SQL and SQL-like dialects and I've never seen syntax like that. It looks like there's a core misunderstanding of what SQL clauses are supposed to do what.

The SELECT clause is for describing a projection of scalar elements. In many dialects you can issue subqueries - sometimes even correlated subqueries - in this clause, but you always must apply an operator that converts the result into a scalar value. For example:

SELECT (CASE WHEN EXISTS (SELECT foo FROM tbl) THEN 1 ELSE 0 END)

Here the "EXISTS" operator converts the uncorrelated subquery into a scalar boolean, so it's legal.

The lack of a FROM clause in the top level query is also a big red flag. It, or some analogue like Oracle's "dual", is legal in about half of the dialects I've seen, but if what you're trying to do is dynamically switch between two queries, you're almost certainly doing it wrong. juergen is right - what you probably meant to do is use 2 different queries.

Collectives™ on Stack Overflow

Syntax Error While Using Case Statement in Spark SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related