com.sap.ip.bi.sdk.dac.relational.query

Interface IBICommandProcessor

All Known Subinterfaces:

IBISapqCommandProcessor

public interface IBICommandProcessor

The Relational Command Processor allows for the manipulation of a query object on a high level without exposing the underlying model.

The commands are based on the SQL language, and they should be relatively straightforward if you have a basic knowledge of SQL. WHERE conditions are specified with an expressions stack in combination with unary or binary operators. For example, you specify the ... WHERE T1.COL1=1 AND (T1.COL2=2 OR T1.COL3=3) condition as follows:

1. pushWhereComparison("T1",col2,IBISQLComparisonOperator.EQUALS, new Integer(3))
2. pushWhereComparison("T1",col2,IBISQLComparisonOperator.EQUALS, new Integer(2))
3. pushWhereLogicalOperator(IBISQLLogicalOperator.OR)
4. pushWhereComparison("T1",col1,IBISQLComparisonOperator.EQUALS, new Integer(1))
5. pushWhereLogicalOperator(IBISQLLogicalOperator.AND)

WHERE conditions are essentially binary trees with binary operators as internal nodes and columns, and values and subselects as leaves. Binary operators in this sense are opposed to unary operators such as NOT ... and ... IS NULL and ternary operators such as ... BETWEEN .. AND ... .

Table aliases (necessary for JOIN operations) are simple String references, generated automatically or user-provided with the addTable(...) methods.

Since:

3.50

Method Summary

String	addColumn(org.omg.cwm.resource.relational.Column column) Adds a column from the table to the result to be retrieved.
String	addColumn(org.omg.cwm.resource.relational.Column column, String tableRef) Same as addColumn(Column), whereby the column is specified by name and reference to an instance of the table used in this query.
String	addColumn(org.omg.cwm.resource.relational.Column column, String tableRef, com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLAggregateFunction aggregateFunction, String columnRef) Same as addColumn(Column, String), whereby an aggregation for this column can be supplied in aggregateFunction.
String	addColumn(org.omg.cwm.resource.relational.Column column, String tableRef, String columnRef) Same as addColumn(Column, String), whereby the label for this column in the result set is supplied in columnRef instead of being automatically generated.
void	addGroupBy(org.omg.cwm.resource.relational.Column column, String tableRef) Groups the result set by the column specified.
void	addOrderBy(org.omg.cwm.resource.relational.Column column, String tableRef) Orders the result by the column and table reference specified.
void	addOrderBy(org.omg.cwm.resource.relational.Column column, String tableRef, boolean ascending) Same as addOrderBy(Column, String), whereby the sort direction is ascending if ascending is true, otherwise descending.
String	addRowCount(String tableRef) Adds a row counter to the result set to be retrieved (equivalent to COUNT(*) in SQL).
String	addRowCount(String tableRef, String columnRef) Same as addRowCount(String), whereby the name (label) of column in the result set can be specified in columnRef.
String	addTable(org.omg.cwm.resource.relational.Table table) Adds a table to the query.
String	addTable(org.omg.cwm.resource.relational.Table table, String tableRef) Same as addTable(Table), whereby in this case the table reference (must be unique for this query) is supplied in tableRef.
void	clear() Resets the query.
ResultSet	execute() Executes the query (retrieving the result).
void	initialize() Resets the query to its initial (empty) state.
void	pushWhereComparison(String tableRef, org.omg.cwm.resource.relational.Column column, com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator oper) Pushes the comparison on to the WHERE stack.
void	pushWhereComparison(String tableRef, org.omg.cwm.resource.relational.Column column, com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator oper, IBIQuery subselect) Pushes the comparison on to theWHEREstack.
void	pushWhereComparison(String tableRef, org.omg.cwm.resource.relational.Column column, com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator oper, List values) Pushes the comparison on to theWHEREstack.
void	pushWhereComparison(String tableRef, org.omg.cwm.resource.relational.Column column, com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator oper, Object value) Pushes the comparison on to the WHERE stack.
void	pushWhereComparison(String tableRef, org.omg.cwm.resource.relational.Column column, com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator oper, Object[] values) Pushes the comparison on to theWHEREstack.
void	pushWhereComparison(String tableRef, org.omg.cwm.resource.relational.Column column, com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator oper, String tableRef2, org.omg.cwm.resource.relational.Column column2) Pushes the comparison on to theWHEREstack.
void	pushWhereLogicalOperator(com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLLogicalOperator oper) Pushes the logical operator onto theWHEREstack.
void	removeColumn(String columnRef) Removes a column from the query identified by its reference.
void	removeTable(String tableRef) Removes a table from the query identified by its reference.
void	removeWhereCondition() Removes the current WHERE condition.
void	synchronize() Synchronizes the command processor with the query.

Method Detail

clear

public void clear()
           throws BIResourceException

Resets the query.

Throws:

BIResourceException - if the operation fails

synchronize

public void synchronize()
                 throws BIResourceException

Synchronizes the command processor with the query.

Throws:

BIResourceException - if an error occurs during the generation of the expression

addColumn

public String addColumn(org.omg.cwm.resource.relational.Column column)
                 throws BIResourceException

Adds a column from the table to the result to be retrieved. Returns the label of this column in the resultset (the column-name with a unique suffix if otherwise non-unique).

Parameters:

column - the column to be added

Returns:

the label of this column in the result set

Throws:

BIResourceException - if the column does not belong to a table that has been previously added

See Also:

Column

addColumn

public String addColumn(org.omg.cwm.resource.relational.Column column,
                        String tableRef)
                 throws BIResourceException

Same as addColumn(Column), whereby the column is specified by name and reference to an instance of the table used in this query.

Parameters:

column - the column to be added

tableRef - the table instance to which this column belongs

Returns:

the label of this column in the result set

Throws:

BIResourceException - if the table reference is unknown or the column is not part of the referenced table

See Also:

addColumn(Column, String), Column

addColumn

public String addColumn(org.omg.cwm.resource.relational.Column column,
                        String tableRef,
                        String columnRef)
                 throws BIResourceException

Same as addColumn(Column, String), whereby the label for this column in the result set is supplied in columnRef instead of being automatically generated.

Parameters:

column - the column to be added

tableRef - the table instance to which this column belongs

columnRef - the label of this column in the result set

Returns:

the label of this column in the result set

Throws:

BIResourceException - if the table reference is unknown or the column is not part of the referenced table

See Also:

Column

addColumn

public String addColumn(org.omg.cwm.resource.relational.Column column,
                        String tableRef,
                        com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLAggregateFunction aggregateFunction,
                        String columnRef)
                 throws BIResourceException

Same as addColumn(Column, String), whereby an aggregation for this column can be supplied in aggregateFunction.

Parameters:

column - the column to be added

tableRef - the table instance to which this column belongs

aggregateFunction - the aggregation to be used on this column (for example, SUM)

columnRef - the label of this column in the result set

Returns:

the label of this column in the result set

Throws:

BIResourceException - if the table reference is unknown or the column is not part of the referenced table

See Also:

Column

addRowCount

public String addRowCount(String tableRef)
                   throws BIResourceException

Adds a row counter to the result set to be retrieved (equivalent to COUNT(*) in SQL).

Notes:

• If tableRef == null, the rows of the result set or of each group of the result set (see addGroupBy(Column, String)) are counted.
• If tableRef != null, only the rows of the table referred by tableRef are counted; in other words, COUNT(tableRef.*) instead of COUNT(*).
• As usual (with SQL), only rows that are NOT NULL are counted. In other words, at least one column of each counted row must be NOT NULL.

Parameters:

tableRef - the table instance to which this counter belongs (can be null)

Returns:

the label of this column in the result set

Throws:

BIResourceException - if the table reference is unknown

Since:

3.50 SP5

addRowCount

public String addRowCount(String tableRef,
                          String columnRef)
                   throws BIResourceException

Same as addRowCount(String), whereby the name (label) of column in the result set can be specified in columnRef.

Parameters:

tableRef - the table instance to which this counter belongs (can be null)

columnRef - the label of this column in the result set

Returns:

the label of this column in the result set

Throws:

BIResourceException - if the table reference is unknown

Since:

3.50 SP5

addGroupBy

public void addGroupBy(org.omg.cwm.resource.relational.Column column,
                       String tableRef)
                throws BIResourceException

Groups the result set by the column specified.

Parameters:

column - the column by which the result set is to be grouped

tableRef - the table instance to which this column belongs

Throws:

BIResourceException - if the table reference is unknown or the column is not part of the referenced table

See Also:

Column

addOrderBy

public void addOrderBy(org.omg.cwm.resource.relational.Column column,
                       String tableRef)
                throws BIResourceException

Orders the result by the column and table reference specified. The sort direction is ascending.

Parameters:

column - the column by which the result set is to be sorted

tableRef - the table instance to which this column belongs

Throws:

BIResourceException - if the table reference is unknown or the column is not part of the referenced table

See Also:

Column

addOrderBy

public void addOrderBy(org.omg.cwm.resource.relational.Column column,
                       String tableRef,
                       boolean ascending)
                throws BIResourceException

Same as addOrderBy(Column, String), whereby the sort direction is ascending if ascending is true, otherwise descending.

Parameters:

column - the column by which the result set is to be sorted

tableRef - the table instance to which this column belongs

ascending - is the sort direction ascending?

Throws:

BIResourceException - if the table reference is unknown or the column is not part of the referenced table

See Also:

Column

addTable

public String addTable(org.omg.cwm.resource.relational.Table table)
                throws BIResourceException

Adds a table to the query. The result of the query is the cross product of all added tables, unless additional restrictions are added, such as JOIN criteria.

Parameters:

table - to add to query

Returns:

reference of this table in the query (to be able to add multiple instances of the same table)

Throws:

BIResourceException - (unused)

See Also:

Table

addTable

public String addTable(org.omg.cwm.resource.relational.Table table,
                       String tableRef)
                throws BIResourceException

Same as addTable(Table), whereby in this case the table reference (must be unique for this query) is supplied in tableRef.

Parameters:

table - to add to query

tableRef - table reference to uniquely identify this table in this query

Returns:

the table reference

Throws:

BIResourceException - (unused)

See Also:

Table

execute

public ResultSet execute()
                  throws BIResourceException

Executes the query (retrieving the result).

Returns:

the result set of the query

Throws:

BIResourceException - in case of errors during the execution

initialize

public void initialize()
                throws BIResourceException

Resets the query to its initial (empty) state.

Throws:

BIResourceException - (unused)

pushWhereComparison

public void pushWhereComparison(String tableRef,
                                org.omg.cwm.resource.relational.Column column,
                                com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator oper,
                                Object value)
                         throws BIResourceException

Pushes the comparison on to the WHERE stack.

Parameters:

tableRef - the table reference to which the column belongs

column - the left-hand-side of the comparison

oper - the comparison operator (

value - the right-hand-side of the comparison

Throws:

BIResourceException - if any one of the following conditions exist:

• column is unknown
• tableRef is unknown
• column does not belong to the table referred to by tableRef
• oper is unknown
• the type of value does not match the type of column
• the expected number of values of oper is not 1

pushWhereComparison

public void pushWhereComparison(String tableRef,
                                org.omg.cwm.resource.relational.Column column,
                                com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator oper)
                         throws BIResourceException

Pushes the comparison on to the WHERE stack.

Parameters:

tableRef - the table reference to which the column belongs

column - the left-hand-side of the comparison

oper - the comparison operator (

Throws:

BIResourceException - if any one of the following conditions hold

• column is unknown
• tableRef is unknown
• column does not belong to the table referred to by tableRef
• oper is unknown
• the expected number of values of oper is not 0

pushWhereComparison

public void pushWhereComparison(String tableRef,
                                org.omg.cwm.resource.relational.Column column,
                                com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator oper,
                                Object[] values)
                         throws BIResourceException

Pushes the comparison on to theWHEREstack.

Parameters:

tableRef - the table reference to which the column belongs

column - the left-hand-side of the comparison

oper - the comparison operator (

values - the right-hand-side of the comparison

Throws:

BIResourceException - if any one of the following conditions hold

• column is unknown
• tableRef is unknown
• column does not belong to the table referred to by tableRef
• oper is unknown
• the type of an element of values does not match the type of column
• the expected number of values of oper is not values.length

pushWhereComparison

public void pushWhereComparison(String tableRef,
                                org.omg.cwm.resource.relational.Column column,
                                com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator oper,
                                List values)
                         throws BIResourceException

Pushes the comparison on to theWHEREstack.

Parameters:

tableRef - the table reference to which the column belongs

column - the left-hand-side of the comparison

oper - the comparison operator (

values - the right-hand-side of the comparison

Throws:

BIResourceException - if any one of the following conditions hold

• column is unknown
• tableRef is unknown
• column does not belong to the table referred to by tableRef
• oper is unknown
• the type of an element of values does not match the type of column
• the expected number of values of oper is not values.size()

pushWhereComparison

public void pushWhereComparison(String tableRef,
                                org.omg.cwm.resource.relational.Column column,
                                com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator oper,
                                IBIQuery subselect)
                         throws BIResourceException

Pushes the comparison on to theWHEREstack.

Parameters:

tableRef - the table reference to which the column belongs

column - the left-hand-side of the comparison

oper - the comparison operator (

subselect - A query with 1 column selected the type of which matches the left-hand-side of the comparison

Throws:

BIResourceException - if any one of the following conditions hold

• column is unknown
• tableRef is unknown
• column does not belong to the table referred to by tableRef
• oper is unknown
• the type of the query subselect does not match the type of column
• the query selects more than one column
• the operator oper can not handle sub-selects

pushWhereComparison

public void pushWhereComparison(String tableRef,
                                org.omg.cwm.resource.relational.Column column,
                                com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator oper,
                                String tableRef2,
                                org.omg.cwm.resource.relational.Column column2)
                         throws BIResourceException

Pushes the comparison on to theWHEREstack.

Parameters:

tableRef - the table reference to which the column belongs

column - the left-hand-side of the comparison

oper - the comparison operator (

tableRef2 - the table reference to which the column belongs

column2 - the right-hand-side of the comparison

Throws:

BIResourceException - if any one of the following conditions hold

• column is unknown
• tableRef is unknown
• column does not belong to the table referred to by tableRef
• column2 is unknown
• tableRef2 is unknown
• column2 does not belong to the table referred to by tableRef2
• oper is unknown
• the type of the column does not match the type of column2
• the operator oper is not a comparison operator

pushWhereLogicalOperator

public void pushWhereLogicalOperator(com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLLogicalOperator oper)
                              throws BIResourceException

Pushes the logical operator onto theWHEREstack.

Parameters:

oper - logical operator (

Throws:

BIResourceException - if the operator is unknown or the stack does not contain the appropriate operands for this operator

removeColumn

public void removeColumn(String columnRef)
                  throws BIResourceException

Removes a column from the query identified by its reference. This will also remove any objects depending on this column instance from the query.

Parameters:

columnRef - reference to the column to be removed

Throws:

BIResourceException - if the column reference is unknown

See Also:

Column

removeTable

public void removeTable(String tableRef)
                 throws BIResourceException

Removes a table from the query identified by its reference. This will also remove any objects depending on this table instance from the query.

Parameters:

tableRef - reference to table instance to be removed

Throws:

BIResourceException - if table reference is not part of query

See Also:

Table

removeWhereCondition

public void removeWhereCondition()
                          throws BIResourceException

Removes the current WHERE condition.

Throws:

BIResourceException - unused