How to write multiple lines in Scala sqlcontext sql query


#1

I have a working query as below:

val results1 = sqlContext.sql(“SELECT product.name AS Product_Name, price, supplier.name AS supplier_Name FROM products_suppliers JOIN product ON products_suppliers.productID = product.productID jOIN supplier ON products_suppliers.supplierID = supplier.supplierID”)

It is too long and I want to seperate it into multiple lines, I tried below methods, neither is working:

val results1 = sqlContext.sql("SELECT product.name AS Product_Name, price, supplier.name AS supplier_Name \
	FROM products_suppliers JOIN product ON products_suppliers.productID = product.productID  \
	jOIN supplier ON products_suppliers.supplierID = supplier.supplierID")


val results1 = sqlContext.sql("""SELECT product.name AS Product_Name, price, supplier.name AS supplier_Name 
	FROM products_suppliers JOIN product ON products_suppliers.productID = product.productID  
	jOIN supplier ON products_suppliers.supplierID = supplier.supplierID""")

What is the right way to do this simple thing? Thank you.


#2

Can you use below command?

var sql = "SELECT product.name AS Product_Name, price, supplier.name AS supplier_Name “
sql =sql+” FROM products_suppliers JOIN product ON products_suppliers.productID = product.productID "
sql =sql+ “jOIN supplier ON products_suppliers.supplierID = supplier.supplierID”

val results1 = sqlContext.sql(sql)


#3

Thank you Vinayak,

That certainly will work, I was wondering if there is way like in sqoop command using ‘’ to separate lines.


#4

I found the right way:

sqlContext.sql("""
	select f.id, f.amount as Amount,
	case when c.name is null or c.name = '' then 'N/A' else c.name end as Course
	from fee f left outer join course c on f.id = c.id
""").show