-2

I am trying to create new dataset via SQL Labs Editor. I have SQL query which is working fine via database manager. However, if I try same query in SQL Lab on Apache Superset it is not working, giving the following error (database I use is Microsoft SQL Server):

Azure Synapse Error mssql error: All your SQL functions need to have an alias on MSSQL. For example: SELECT COUNT(*) AS C1 FROM TABLE1 This may be triggered by: Issue 1002 - The database returned an unexpected error.

This is the query:

SELECT cast(I04_OP_DATA as date), I04_OP_RUSIS, SUM(I05_SUMA)
FROM I04_ATH
LEFT JOIN I05_ATD 
ON I04_ATH.I04_KODAS_CH  = I05_ATD.I05_KODAS_CH
GROUP BY cast(I04_OP_DATA as date), I04_OP_RUSIS;
Dale K
  • 25,246
  • 15
  • 42
  • 71
Lukas Kondrusevičius
  • 25
  • 3
  • 5
    Have you tried aliasing your columns as the error says? – Dale K Jun 20 '23 at 09:08
  • 2
    Sql server doesn't autocreate names for columns containing expressions, and probably this dataset application you use requires proper names. Add alias to all columns like: cast(I04_OP_DATA as date) as alias_name_without_spaces – siggemannen Jun 20 '23 at 09:48
  • 1
    Issue 1002 isn't an SQL Server-related error. I think you'll find it's coming from whatever Python library SQLAlchemy is using under the covers, probably something DBAPI-based. – AlwaysLearning Jun 20 '23 at 09:59

1 Answers1

1

Try providing names for the new variables you create. For instance this works for me in SQL Server but not Apache Superset SQL Lab:

SELECT MAX([my_variable])
FROM my_table

But this works on both:

SELECT MAX([my_variable]) as X
FROM my_table

Though the error message I usually see is "Microsoft SQL Server Error - no field of name". I'm running Superset 2.1.0. FYI there's a Superset discussion on this: https://github.com/apache/superset/discussions/23213

Sam Firke
  • 21,571
  • 9
  • 87
  • 105