Advanced Designer Learning Path is now live! Read More

Dataset SQL Query

Level 1
Dataset SQL Query

Hello, how do I remove the "Top N" clause added by Dataiku when my new dataset is set up by using SQL query Mode. Here is the error...

"Failed to read data from DB
Failed to read data from DB, caused by: SQLException: [Teradata Database] [TeraJDBC 16.00.00.24] [Error 6916] [SQLState HY000] TOP N Syntax error: Top N option is not supported with QUALIFY clause.
HTTP code: 500, type: com.dataiku.dip.exceptions.DataStoreIOException"

Thanks!

0 Kudos
3 Replies
Community Manager
Community Manager

Hi, @ls! Can you provide any further details on the thread to assist users in helping you find a solution (insert examples like DSS version etc.) Also, can you let us know if you’ve tried any fixes already?This should lead to a quicker response from the community.

Looking for more resources to help you use DSS effectively and upskill your knowledge? Check out these great resources: Dataiku Academy | Documentation | Knowledge Base

A reply answered your question? Mark as ‘Accepted Solution’ to help others like you!
0 Kudos
Level 1
Author

I created a dataset by using an sql query like this...

select c1, c2, c3, date1
from t1
qualify row_number() over(partition by c1 order by date1 desc) = 1

The source, Teradata, returned with a Top N error. I didn't put top n into my query so I believed Dataiku did so.

I solved it by putting the query in a derived table like this...

select *
from
( select c1, c2, c3, date1
  from t1
  qualify row_number() over(partition by c1 order by date1 desc) = 1
) a

Any other suggestions?

0 Kudos
Neuron
Neuron

Hi @ls,

If you need to use a dataset that represents a SQL query then your solution is a good one.

I don't often use datasets defined in this way since they can't be used with downstream SQL recipes (and maybe other types of recipes).  

Another perhaps obvious option is to define a SQL table dataset (rather than a SQL query dataset) that points to t1 and then use a subsequent SQL recipe containing the qualify logic to create a new dataset / table. The drawback is that you'll end up with a second table.

A variation on this is that you could include the qualify logic to create a temp table at the beginning of a downstream recipe and avoid the second table.

Note that if you use a SQL Script type SQL recipe, you don't need to specify an input dataset. You could reference t1 in such a recipe directly without first creating a SQL table dataset.

Marlan  

 

  

0 Kudos
A banner prompting to get Dataiku DSS