CODEWITHSUNDEEP
sqlplayframework-2.0teradataebeandialect
Capacytron
Capacytron

Reputation: 3739

ebean in play 2 framework with SQL dialect

I'm trying to establish friendship between Teradata 13.10 and play 2 framework using ebean ORM layer. My app does try to query DB:

 select t0.workflow_id c0, t0.CHNL_TYPE_CD c1, t0.WORKFLOW_NAME c2, t0.INFO_SYSTEM_TYPE_CD c3, t0.FOLDER_NAME c4 from ETL_WORKFLOW t0 order by name limit 11

The problem is... that Teradata does know nothing about LIMIT Is there any possibility to find implementation/override something and make underlying ORM work with Teradata?

UPD: Seems like I have to do something with tese classes: http://www.avaje.org/static/javadoc/pub/index.html I'm looking for samples: 1. Set proper SQL dialect for ebean or make it work in SQL ANSI mode. 2. Override classes for ebean and write own implementation of LIMIT functionality.

Upvotes: 0

Views: 1004

Answers (1)

Rob Paller
Rob Paller

Reputation: 7786

Teradata supports both the TOP n operator and SAMPLE clause in your SELECT statement. TOP n being an extension to the ANSI SQL 2008 standard is the closest equivalent to the LIMIT n operator you are looking for.

TOP n is processed after all the other clauses in your SELECT statement have been satisfied. It is the functional replacement of using the QUALIFY ROW_NUMBER() or QUALIFY RANK() window aggregates to accomplish the same task providing at worst equivalent performance of the window aggregate functions.

SAMPLE provides you some additional flexibility by allowing you to returned multiple sample sets within a single result. It can also be used for simple, random samples from a resultset as well. Given the options that are available with SAMPLE you are best served with referring to the SQL Data Manipulation Language manual for Teradata for all of the details. The Teradata manuals can be downloaded from here. Just select which version of Teradata you wish to download the manuals for.

Edit: Using the RawSQL feature with Ebean you may be able use either the SAMPLE or TOP n operators in your SQL explicitly and not allow Ebean to add expressions such as the LIMIT OFFSET clause automatically. Have you tried this approach yet?

Upvotes: 0

Related Questions