SqlQuery (Pentaho Community Edition Project: mondrian 3.14.0.0-12 API)

java.lang.Object
- mondrian.rolap.sql.SqlQuery

```
public class SqlQuery
extends Object
```
SqlQuery allows us to build a select statement and generate it in database-specific SQL syntax.
Notable differences in database syntax are:

Identifier quoting

Oracle (and all JDBC-compliant drivers) uses double-quotes, for example select * from "emp". Access prefers brackets, for example select * from [emp]. mySQL allows single- and double-quotes for string literals, and therefore does not allow identifiers to be quoted, for example select 'foo', "bar" from emp.

AS in from clause

Oracle doesn't like AS in the from * clause, for example select from emp as e vs. select * from emp e.

Column aliases

Some databases require that every column in the select list has a valid alias. If the expression is an expression containing non-alphanumeric characters, an explicit alias is needed. For example, Oracle will barfs at select empno + 1 from emp.

Parentheses around table names

Oracle doesn't like select * from (emp)

Queries in FROM clause

PostgreSQL and hsqldb don't allow, for example, select * from (select * from emp) as e.

Uniqueness of index names

In PostgreSQL and Oracle, index names must be unique within the database; in Access and hsqldb, they must merely be unique within their table

Datatypes

In Oracle, BIT is CHAR(1), TIMESTAMP is DATE. In PostgreSQL, DOUBLE is DOUBLE PRECISION, BIT is BOOL.

NOTE: Instances of this class are NOT thread safe so the user must make sure this is accessed by only one thread at a time.

Author:: jhyde

Nested Class Summary

Nested Classes
Modifier and Type Class and Description

static class SqlQuery.CodeSet
Collection of alternative code for alternative dialects.

Nested Classes
Modifier and Type	Class and Description
`static class`	`SqlQuery.CodeSet` Collection of alternative code for alternative dialects.

Constructor Summary

Constructors
Constructor and Description
`SqlQuery(Dialect dialect)` Creates a SqlQuery using a given dialect and inheriting the formatting preferences from `MondrianProperties.GenerateFormattedSql` property.
`SqlQuery(Dialect dialect, boolean formatted)` Base constructor used by all other constructors to create an empty instance.

Method Summary

All Methods Static Methods Instance Methods Concrete Methods
Modifier and Type	Method and Description
`boolean`	`addFrom(MondrianDef.RelationOrJoin relation, String alias, boolean failIfExists)` Adds a relation to a query, adding appropriate join conditions, unless it is already present.
`void`	`addFrom(SqlQuery sqlQuery, String alias, boolean failIfExists)`
`boolean`	`addFromQuery(String query, String alias, boolean failIfExists)` Adds a subquery to the FROM clause of this Query with a given alias.
`void`	`addGroupBy(String expression)`
`void`	`addGroupBy(String expression, String alias)`
`void`	`addGroupingFunction(String columnExpr)`
`void`	`addGroupingSet(List<String> groupingColumnsExpr)`
`void`	`addHaving(String expression)`
`void`	`addOrderBy(String expr, boolean ascending, boolean prepend, boolean nullable)` Adds an item to the ORDER BY clause.
`void`	`addOrderBy(String expr, String alias, boolean ascending, boolean prepend, boolean nullable, boolean collateNullsLast)` Adds an item to the ORDER BY clause.
`String`	`addSelect(String expression, SqlStatement.Type type)` Adds an expression to the select clause, automatically creating a column alias.
`String`	`addSelect(String expression, SqlStatement.Type type, String alias)` Adds an expression to the select clause, with a specified type and column alias.
`String`	`addSelectGroupBy(String expression, SqlStatement.Type type)` Adds an expression to the SELECT and GROUP BY clauses.
`void`	`addWhere(RolapStar.Condition joinCondition)`
`void`	`addWhere(String expression)`
`void`	`addWhere(String exprLeft, String exprMid, String exprRight)`
`SqlQuery`	`cloneEmpty()` Creates an empty `SqlQuery` with the same environment as this one.
`String`	`getAlias(String expression)`
`int`	`getCurrentSelectListSize()`
`Dialect`	`getDialect()`
`boolean`	`isSupported()`
`static SqlQuery`	`newQuery(DataSource dataSource, String err)`
`String`	`nextColumnAlias()`
`void`	`registerRootRelation(MondrianDef.RelationOrJoin root)`
`void`	`setAllowHints(boolean t)` Chooses whether table optimization hints may be used (assuming the dialect supports it).
`void`	`setDistinct(boolean distinct)`
`void`	`setSupported(boolean supported)`
`void`	`toBuffer(StringBuilder buf, String prefix)` Writes this SqlQuery to a StringBuilder with each clause on a separate line, and with the specified indentation prefix.
`Pair<String,List<SqlStatement.Type>>`	`toSqlAndTypes()`
`String`	`toString()`

Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

- Constructor Detail
  - SqlQuery
```
public SqlQuery(Dialect dialect,
                boolean formatted)
```
    Base constructor used by all other constructors to create an empty instance.
    
    Parameters:
    
    dialect - Dialect
    
    formatted - Whether to generate SQL formatted on multiple lines
  - SqlQuery
```
public SqlQuery(Dialect dialect)
```
    Creates a SqlQuery using a given dialect and inheriting the formatting preferences from MondrianProperties.GenerateFormattedSql property.
    
    Parameters:
    
    dialect - Dialect
- Method Detail
  - cloneEmpty
```
public SqlQuery cloneEmpty()
```
    Creates an empty SqlQuery with the same environment as this one. (As per the Gang of Four 'prototype' pattern.)
  - setDistinct
```
public void setDistinct(boolean distinct)
```
  - setAllowHints
```
public void setAllowHints(boolean t)
```
    Chooses whether table optimization hints may be used (assuming the dialect supports it).
    
    Parameters:
    
    t - True to allow hints to be used, false otherwise
  - addFromQuery
```
public boolean addFromQuery(String query,
                            String alias,
                            boolean failIfExists)
```
    Adds a subquery to the FROM clause of this Query with a given alias. If the query already exists it either, depending on failIfExists, throws an exception or does not add the query and returns false.
    
    Parameters:
    
    query - Subquery
    
    alias - (if not null, must not be zero length).
    
    failIfExists - if true, throws exception if alias already exists
    
    Returns:
    
    true if query *was* added
  - addFrom
```
public void addFrom(SqlQuery sqlQuery,
                    String alias,
                    boolean failIfExists)
```
  - addFrom
```
public boolean addFrom(MondrianDef.RelationOrJoin relation,
                       String alias,
                       boolean failIfExists)
```
    Adds a relation to a query, adding appropriate join conditions, unless it is already present.
    Returns whether the relation was added to the query.
    
    Parameters:
    
    relation - Relation to add
    
    alias - Alias of relation. If null, uses relation's alias.
    
    failIfExists - Whether to fail if relation is already present
    
    Returns:
    
    true, if relation *was* added to query
  - addSelect
```
public String addSelect(String expression,
                        SqlStatement.Type type)
```
    Adds an expression to the select clause, automatically creating a column alias.
  - addSelectGroupBy
```
public String addSelectGroupBy(String expression,
                               SqlStatement.Type type)
```
    Adds an expression to the SELECT and GROUP BY clauses. Uses the alias in the GROUP BY clause, if the dialect requires it.
    
    Parameters:
    
    expression - Expression
    
    Returns:
    
    Alias of expression
  - getCurrentSelectListSize
```
public int getCurrentSelectListSize()
```
  - nextColumnAlias
```
public String nextColumnAlias()
```
  - addSelect
```
public String addSelect(String expression,
                        SqlStatement.Type type,
                        String alias)
```
    Adds an expression to the select clause, with a specified type and column alias.
    
    Parameters:
    
    expression - Expression
    
    type - Java type to be used to hold cursor column
    
    alias - Column alias (or null for no alias)
    
    Returns:
    
    Column alias
  - getAlias
```
public String getAlias(String expression)
```
  - addWhere
```
public void addWhere(String exprLeft,
                     String exprMid,
                     String exprRight)
```
  - addWhere
```
public void addWhere(RolapStar.Condition joinCondition)
```
  - addWhere
```
public void addWhere(String expression)
```
  - addGroupBy
```
public void addGroupBy(String expression)
```
  - addGroupBy
```
public void addGroupBy(String expression,
                       String alias)
```
  - addHaving
```
public void addHaving(String expression)
```
  - addOrderBy
```
public void addOrderBy(String expr,
                       boolean ascending,
                       boolean prepend,
                       boolean nullable)
```
    Adds an item to the ORDER BY clause.
    
    Parameters:
    
    expr - the expr to order by
    
    ascending - sort direction
    
    prepend - whether to prepend to the current list of items
    
    nullable - whether the expression might be null
  - addOrderBy
```
public void addOrderBy(String expr,
                       String alias,
                       boolean ascending,
                       boolean prepend,
                       boolean nullable,
                       boolean collateNullsLast)
```
    Adds an item to the ORDER BY clause.
    
    Parameters:
    
    expr - the expr to order by
    
    alias - the alias of the column, as returned by addSelect
    
    ascending - sort direction
    
    prepend - whether to prepend to the current list of items
    
    nullable - whether the expression might be null
    
    collateNullsLast - whether null values should appear first or last.
  - toString
```
public String toString()
```
    Overrides:
    
    toString in class Object
  - toBuffer
```
public void toBuffer(StringBuilder buf,
                     String prefix)
```
    Writes this SqlQuery to a StringBuilder with each clause on a separate line, and with the specified indentation prefix.
    
    Parameters:
    
    buf - String builder
    
    prefix - Prefix for each line
  - getDialect
```
public Dialect getDialect()
```
  - newQuery
```
public static SqlQuery newQuery(DataSource dataSource,
                                String err)
```
  - addGroupingSet
```
public void addGroupingSet(List<String> groupingColumnsExpr)
```
  - addGroupingFunction
```
public void addGroupingFunction(String columnExpr)
```
  - toSqlAndTypes
```
public Pair<String,List<SqlStatement.Type>> toSqlAndTypes()
```
  - registerRootRelation
```
public void registerRootRelation(MondrianDef.RelationOrJoin root)
```
  - isSupported
```
public boolean isSupported()
```
  - setSupported
```
public void setSupported(boolean supported)
```

Class SqlQuery

Nested Class Summary

Constructor Summary

Method Summary

Methods inherited from class java.lang.Object

Constructor Detail

SqlQuery

SqlQuery

Method Detail

cloneEmpty

setDistinct

setAllowHints

addFromQuery

addFrom

addFrom

addSelect

addSelectGroupBy

getCurrentSelectListSize

nextColumnAlias

addSelect

getAlias

addWhere

addWhere

addWhere

addGroupBy

addGroupBy

addHaving

addOrderBy

addOrderBy

toString

toBuffer

getDialect

newQuery

addGroupingSet

addGroupingFunction

toSqlAndTypes

registerRootRelation

isSupported

setSupported