spark sql issue with passing parameters

Question

i am trying to convert below in spark

val forX=spark.sql(""" select *  from abc where tv='Dish' and to_date(acd)>='2022-10-11 where  indicator ='X' """)
val forY=spark.sql(""" select *  from abc where tv='Dish' and to_date(acd)>='2022-10-11 where  indicator ='Y' """)
val forZ=spark.sql(""" select *  from abc where tv='Dish' and to_date(acd)>='2022-10-11 where  indicator ='Z' """)

the above dataframe gives out as


val Mylist:List[String]=List("X","Y","S","I")

for (i <- Mylist)
   val eindicator =spark.sql(""" select * from abc where tv="Dish" and to_date(acd)>='2022-10-11'
and indicator=${i} """)

println("#############",eindicator)

its giving as cannot resolve 'X' from input column

Any suggestions how to pass that $i value ?

Answer 1

Wrap the indicator value in single quotes so SQL analyser sees that i is a string literal rather than a column name.

Please see the following example from a Spark shell.

scala> Seq(("value1", 1), ("value2", 2), ("value3", 3)).toDF("value1", "value2").createOrReplaceTempView("t")
scala> spark.sql("select * from t").show
+------+------+
|value1|value2|
+------+------+
|value1|     1|
|value2|     2|
|value3|     3|
+------+------+
scala> spark.sql("select * from t where value1 = value2").show // Show me rows with the value in the first column equal to the value in the second column.
+------+------+
|value1|value2|
+------+------+
+------+------+
scala> spark.sql("select * from t where value1 = 'value2'").show  // Show me rows with the value in the first column equal to the string literal 'value2'.
+------+------+
|value1|value2|
+------+------+
|value2|     2|
+------+------+

For more information, please see the What is the difference between single and double quotes in SQL? discussion.