SCALA TERADATA SQL Exception

Question

I am new to teradata and also scala. I am executing a query in scala which is throwing SQL Exception but the same query is running fine in Teradata studio.

Below is the query:

 SELECT COUNT(loan_no)
    ,field1
    ,field2
    ,field3
FROM testdb.loan
WHERE field1 < DATE '2017-09-30'
GROUP BY field1
    ,field2
    ,field3
ORDER BY field1

Error received is:

java.sql.SQLException: [Teradata Database] [TeraJDBC 15.00.00.20] [Error 3707] [SQLState 42000] Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword or '(' between the 'FROM' keyword and the 'Select' keyword.

Scala code

val queryString = "SELECT COUNT(loan_no),field1,field2,field3 FROM testdb.loan WHERE field1 < DATE '2017-09-30' GROUP BY field1,field2,field3 ORDER BY field1"

val connString = 
"jdbc:teradata://test.udaystd.com/,TMODE=TERA,charset=UTF8, 
 user="+userid+", password="+passwd+", ,charset=UTF8"

val dbdriver = "com.teradata.jdbc.TeraDriver"

val jddf = sqlContext.load("jdbc",
  Map("url" -> connString,
   "dbtable" -> queryString,
   "driver" -> dbdriver))

Please help.

Can you show the Scala code that includes this query? Sounds like this might be an issue with the way you create the string literal. — Tzach Zohar
i tried that too and result is same error. The only way it works is when i have it like ( (select * from testdb.loan) as loandata) — Udayakiran Vichinthangal

Udayakiran Vichinthangal Udayakiran Vichinthangal · Accepted Answer · 2017-10-06T21:26:51

I was able to fix by using the below code

val queryString = " (SELECT COUNT(loan_no) as loancnt 
,field1,field2,field3 FROM testdb.loan WHERE field1 < DATE '2017-09-30' 
GROUP BY field1,field2,field3) as LOANDATA"

Also, i could not use order by. So i ordered it after getting it in dataframe.

val orderedDF = df.orderBy($"field1".desc)

SCALA TERADATA SQL Exception

1 Answers