Package net.pricefx.formulaengine
Interface DatamartContext.SqlQuery
- Enclosing interface:
DatamartContext
public static interface DatamartContext.SqlQuery
-
Method Summary
Modifier and TypeMethodDescriptionaddSource
(DatamartContext.Query sourceQuery) Adds aDatamartContext.Query
as a source for this SQL query.addSource
(DatamartContext.Query sourceQuery, String alias) Adds aDatamartContext.Query
as a source for this SQL query.addSource
(DatamartContext.Table table) Adds aDatamartContext.Table
as a source for this SQL query.addSource
(DatamartContext.Table table, String alias) Adds aDatamartContext.Table
as a source for this SQL query.Adds a with clause to the SQL query, where every "?"Adds a with clause to the SQL query, where every "?"setAllowUnknownFunctions
(boolean allowUnknownFunctions) Allow to use SQL functions that are not recognized by the validation.setMaxRows
(Integer maxRows) Sets the max rows to be returned when executing the SQL query.Sets the main SQL SELECT clause of this query, where every "?"
-
Method Details
-
addSource
Adds aDatamartContext.Query
as a source for this SQL query. Convenience method which generates the alias for you, starting with "T1", and then incrementing for each additional source and with clause.- Parameters:
sourceQuery
- query to use as a source for this sql query- Returns:
- this query
-
addSource
Adds aDatamartContext.Query
as a source for this SQL query.- Parameters:
sourceQuery
- query to use as a source for this sql queryalias
- the alias to assign to this source query- Returns:
- this query
-
addSource
Adds aDatamartContext.Table
as a source for this SQL query. Convenience method which generates the alias for you, starting with "T1", and then incrementing for each additional source and with clause.- Parameters:
table
- table to use as a source for this sql query- Returns:
- this query
-
addSource
Adds aDatamartContext.Table
as a source for this SQL query.- Parameters:
table
- table to use as a source for this sql queryalias
- the alias to assign to this source table- Returns:
- this query
-
addWith
Adds a with clause to the SQL query, where every "?" char gets replaced by the values provided as additional parameters. This avoids type conversions that would typically happen if the whole query was constructed as a single String. Convenience method which generates the alias for you, starting with "T1", and then incrementing for each additional source query and with clause.Example:
def with = """SELECT T1.product, T1.revenue AS ActualRevenue, T2.revenue AS ForecastRevenue T1.volume AS ActualVolume, T2.volume AS ForecastVolume FROM T1 LEFT OUTER JOIN T2 USING (product) WHERE T1.PG = ?""" sqlQuery.addWith(with, "PG-ABC") // binding some product group value; with-clause gets assigned the T3 alias
- Parameters:
withClause
- sql SELECT code to use as WITH clause in this querybindings
- parameters to insert for every "?" that appears in the with clause- Returns:
- this query
-
addWith
Adds a with clause to the SQL query, where every "?" char gets replaced by the values provided as additional parameters. This avoids type conversions that would typically happen if the whole query was constructed as a single String. Convenience method which generate the alias for you, starting with "T1", and then incrementing for each additional source query and with clause.Example:
def with = """SELECT T1.product, T1.revenue AS ActualRevenue, T2.revenue AS ForecastRevenue T1.volume AS ActualVolume, T2.volume AS ForecastVolume FROM T1 LEFT OUTER JOIN T2 USING (product) WHERE T1.PG = ?""" sqlQuery.addWith(with, "W1", "PG-ABC") // binding some product group value
- Parameters:
withClause
- sql SELECT code to use as WITH clause in this queryalias
- the alias to assign to this with clausebindings
- parameters to insert for every "?" that appears in the with clause- Returns:
- this query
-
setQuery
Sets the main SQL SELECT clause of this query, where every "?" char gets replaced by the values provided as additional parameters. This avoids type conversions that would typically happen if the whole query was constructed as a single String.Example:
def sql = "SELECT SUM(ActualRevenue) - SUM(ForecastRevenue) FROM T3" sqlQuery.setQuery(sql)
- Parameters:
sql
- main SELECT clause of this querybindings
- parameters to insert for every "?" that appears in the with clause- Returns:
- this query
-
setMaxRows
Sets the max rows to be returned when executing the SQL query. Note that the total resulset needs to fit in memory. When not set or set to 0, the default max, as determined by thedatamart.query.externalRowsLimit
instance param is used. The default value of this param is 1 million rows.
Obviously, the SQL statement can also specify a LIMIT clause, but it should be noted that the max of 1 million rows will still apply.- Parameters:
maxRows
-- Returns:
- this query
-
setAllowUnknownFunctions
Allow to use SQL functions that are not recognized by the validation.Use with caution, as the underlying database engine used in PA can vary depending on the cluster, it is best to use SQL functions known to be supported by all the supported database engines.
- Parameters:
allowUnknownFunctions
- a boolean- Returns:
- this query
-