Interface DatamartContext.SqlQuery

  • Enclosing interface:
    DatamartContext

    public static interface DatamartContext.SqlQuery
    • Method Detail

      • 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.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
      • 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