Package net.pricefx.formulaengine

Interface DatamartContext.SqlQuery

Enclosing interface:
DatamartContext
public static interface DatamartContext.SqlQuery

  • Method Details

    • addSource

      DatamartContext.SqlQuery addSource(DatamartContext.Query sourceQuery)
      Adds a DatamartContext.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

      DatamartContext.SqlQuery addSource(DatamartContext.Query sourceQuery, String alias)
      Adds a DatamartContext.Query as a source for this SQL query.
      Parameters:
      sourceQuery - query to use as a source for this sql query
      alias - the alias to assign to this source query
      Returns:
      this query

    • addSource

      DatamartContext.SqlQuery addSource(DatamartContext.Table table)
      Adds a DatamartContext.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

      DatamartContext.SqlQuery addSource(DatamartContext.Table table, String alias)
      Adds a DatamartContext.Table as a source for this SQL query.
      Parameters:
      table - table to use as a source for this sql query
      alias - the alias to assign to this source table
      Returns:
      this query

    • addWith

      DatamartContext.SqlQuery addWith(String withClause, Object... bindings)
      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 query
      bindings - parameters to insert for every "?" that appears in the with clause
      Returns:
      this query

    • addWith

      DatamartContext.SqlQuery addWith(String withClause, String alias, Object... bindings)
      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 query
      alias - the alias to assign to this with clause
      bindings - parameters to insert for every "?" that appears in the with clause
      Returns:
      this query

    • setQuery

      DatamartContext.SqlQuery setQuery(String sql, Object... bindings)
      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 query
      bindings - parameters to insert for every "?" that appears in the with clause
      Returns:
      this query

    • setMaxRows

      DatamartContext.SqlQuery setMaxRows(Integer maxRows)
      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 the datamart.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

      DatamartContext.SqlQuery setAllowUnknownFunctions(boolean allowUnknownFunctions)
      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