Error in Fetching Data from DB using the Input tool

Question

Hello Community ,

Using the Input Tool I was able to connect to the DB using the ODBC driver.

I was able to run a simple query of SELECT * FROM table name WHERE condition

But if I make a error in syntax say SELECT * FROM table name WHERE ;; condition

The Input Tool throws out an error Saying Error opening "......: No Columns returned "

The error message does not give any information as to what is causing the error.

So in the actual business case there is a complex query which is currently being run in the Microsoft SQL Studio to fetch the data and then further operations on it are performed in excel.

The same query gives an error in alteryx with the generic message of No columns returned

Any idea how to find what part of the SQL query is giving the error ? and to debug it ?

The query in picture is complex and is not feasible to built it from scratch in alteryx nor is it feasible to bring the tables from DB in workflow

The same issue persist in the IN DB Tool ,any help on how to debug the error would be much appreciated.

PS : I can not share the query here

Thanks

belliott4 · Answer

Prefacing by recognizing this thread is two and a half years old, and perhaps Alteryx does this now.  But, Pentaho Data Integration returns sql error details that are incredibly helpful to debug. If it can do it, Alteryx should be able to.

Varun3 · Answer

Hey I get your point,but as I had mentioned the query works without any errors in other tools such as Microsoft SQL studio it is throwing up an error in alteryx and not in other tools .

The query is already in production in my organisation .

Thanks

Qiu · Answer

@Varun3 
Honestly, I think it is not really fair to ask Alteryx to help you debug the SQL query.
It would assme your query works at first.
It is quite straightforward we prepare our query with the provoide SQL editor along with the database. Management Studio in the case of SQL Server, would give you much more details about your errors if any.
After the query is working, we can then bring it to Alteryx with no hustle.

For your consideration.