Usage

public List<Column>getColumnList()

Description

This method is used to get a list of the target column objects for the insert statement. Each column contains information about the name, datatype, and size of the target column. Also the query select item that is used to load this column can be determined.

Example 1

A test task is set up in an IKM that has IKM Oracle Insert as a base KM.

def colList = INSERT.getColumnList()
colListStr = ''

for (Column col : colList) {
  if (colListStr.length() != 0) colListStr += ",\n"
  colListStr += col.getTable().getQualifier() + '.' + col.getTable().getName()
  colListStr += " /* Will be loaded from " + (col.getSourceSelectItem()  == null ? "unknown" : col.getSourceSelectItem()) + " */"
}

Generated column list string = $[colListStr]

Generated column list string =
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Stock_Name" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Stock_Symbol" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Stock_Type" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Industry_Type" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."SP_Rating" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Company_Name" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Registered_Address" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Registered_City" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Registered_State" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Postal_Code" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Registered_County" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."FaceValue" */

Example 2:

To set up for the example, a new Flex Field object is set up for “Attribute” objects in the Security navigator under the “Objects” accordion. The new Flex Field is called “ATTR_SUFFIX”. Also the source datastore model has a non-default value set for 3 of its attributes. The values are “_KEY1”, “_KEY2”, and “_KEY3”.

Also a new boolean KM option called “ADD_SUFFIXES” is added to test KM, and the value of the new option is set to true for the KM instance that is assigned to the target node in the test mapping. A test task is set up in an IKM that has IKM Oracle Insert as a base KM.

def colList = INSERT.getColumnList()
colListStr = ''
addSuffixes = physicalNode.getKMOptionValueBoolean("ADD_SUFFIXES")
for (Column col : colList) {
  if (colListStr.length() != 0) colListStr += ",\n"
  attrs = col.getSourceSelectItem().getSourceAttributes()
  String suffix = "";
  if (addSuffixes && attrs != null && attrs.size() > 0) {
    suffix = attrs.get(0).getFlexFieldValue("ATTR_SUFFIX")
  }
  colListStr += col.getTable().getQualifier() + '.' + col.getTable().getName() + "." + '\"' + col.getUnquotedName() + suffix + '\"'
}

Column List string with suffixes from source flex fields: $[colListStr]

Column List string with suffixes from source flex fields:
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Stock_Name",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Stock_Symbol_CUSTOM_KEY2",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Stock_Type",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Industry_Type_CUSTOM_KEY3",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."SP_Rating",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Company_Name_CUSTOM_KEY1",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Registered_Address",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Registered_City",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Registered_State",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Postal_Code",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Registered_County",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."FaceValue"

Usage

public SqlQuery getQuery()

Description

This method is used to get the main source query object from the target IKM or LKM API object. The SqlQueryobject contains all metadata necessary to construct a SQL query. A query may be recursively defined, so that the FromClauseobject owned by the query object may contain other SqlQuery objects that represent subqueries of the top-level query.

Example

The return value of the getQuery() methodon the top level INSERTobject for a SQL Insert KM should be the same as the value of the built-in QUERYvariable. A simple test for that is to create a task like this:

sourceQuery = INSERT.getQuery() queryEqualsString = (sourceQuery.equals(QUERY) ? "equal to" : "not equal to")

The return value from INSERT.getQuery() is $[queryEqualsString] to the built-in QUERY variable.

-- The first subquery is:
SELECT 
  EMP.EMP_NO AS EMP_NO ,
  EMP.LAST_NAME AS LAST_NAME ,
  EMP.FIRST_NAME AS FIRST_NAME ,
  EMP.DEPT_ID AS DEPT_ID   
FROM /* Top-level from clause */  
  ODI_SRC.EMP@NEXTGEN_TEST_ORACLE_SRC EMP  
GROUP BY
  EMP.EMP_NO,EMP.LAST_NAME,EMP.FIRST_NAME,EMP.DEPT_ID 
-- The source mapping physical node for the subquery is JOIN1  

Usage

public List<SqlQuery>getSubqueries ()

Description

This method is used to get the set of subqueries that are defined by a query. This method returns only the first level subqueries. Each subquery returned may also contain other subqueries, that can be retrieved using the same method

Example

The example retrieves the subqueries if found, and displays the subquery text of the first subquery.

subqueries = QUERY.getSubqueries()
subquery = null;
if (subqueries != null && subqueries.size() > 0) {
  subquery = QUERY.getSubqueries().get(0)
} else {
  odiRef.warn("No subquery found")
}
println("In task groovy, subquery is " + subquery)
subquerySourceNodeName = (subquery == null ? "unknown" : subquery.getMapPhysicalNode().getName())
println("In task groovy, subquerySourceNodeName is" + subquerySourceNodeName)

The first subquery is: {#IF ($[subquery] != null) #} $[subquery.getText()] {#ELSE#} not found {# ENDIF #}. The source mapping physical node for the subquery is $[subquerySourceNodeName]

-- The first subquery is:
SELECT 
  EMP.EMP_NO AS EMP_NO ,
  EMP.LAST_NAME AS LAST_NAME ,
  EMP.FIRST_NAME AS FIRST_NAME ,
  EMP.DEPT_ID AS DEPT_ID   
FROM /* Top-level from clause */  
  ODI_SRC.EMP@NEXTGEN_TEST_ORACLE_SRC EMP  
GROUP BY
  EMP.EMP_NO,EMP.LAST_NAME,EMP.FIRST_NAME,EMP.DEPT_ID 
-- The source mapping physical node for the subquery is JOIN1  

Usage

public Table getTargetTable() ()

Description

This method is used to get the substitution API object that represents a target table. The return type is Table, which provides methods to get the name and characteristics of the target table.

Example

The example generates a SQL DDL CREATEstatement to create the table.

table = INSERT.getTargetTable()
tableName = table.getCreationName()
tableQualifier = physicalNode.getLocation() == null ? null : physicalNode.getLocation().getName();
odiRuntimeAccessName = OdiRef.getOdiGeneratedAccessName("TARG_NAME", physicalNode, "A");
tableAlias = component.getAlias();
tgtColList = { 
  def cols = component.getAttributes();
  String result = ""
  def first = true
  for (col in cols) {
    if (!first) result += ",\n"
    result += col.getSQLAccessName(false, "") + " " + MappingUtils.getDdlDataType(col.getBoundObject());
    first = false
  }
  return result
}

create table $[odiRuntimeAccessName]
(
  $[tgtColList.call()]
)

create table COMPKM_TEST_TGT_SCHEMA."Stock_Dim"
(
  "Stock_Key_PK" NUMBER(30),
"Split_Key" NUMBER(30),
"Stock_Name" VARCHAR2(50),
"Stock_Symbol" VARCHAR2(50),
"Stock_Type" VARCHAR2(50),
"Industry_Type" VARCHAR2(50),
"SP_Rating" VARCHAR2(50),
"Company_Name" VARCHAR2(50),
"Registered_Address" VARCHAR2(50),
"Registered_City" VARCHAR2(50),
"Registered_State" VARCHAR2(50),
"Postal_Code" VARCHAR2(50),
"Registered_County" VARCHAR2(50),
"FaceValue" NUMBER(30)
)

Usage

public List<FromClause> getFromList()

Description

Example

The example gets the main FROM clause objects and derives a list of all the source tables using the FromClause.getSourceTables() call.

sourceTables = QUERY.getFromList().get(0).getSourceTables()
sourceTableNames = ''
for (sourceTable in sourceTables) {
  if (sourceTableNames.length() > 0) sourceTableNames += ", ";
  sourceTableNames += sourceTable.getName();
}

The source tables are: $[sourceTableNames]

The source tables are: EMP, DEPT

Usage

public List<ISelectItem> getSelectList()

Description

This method is used to get the list of select items present in the SQL query object. Each item is represented by an interface instance of IselectItem. The implementations of ISelectItem are ArrayExpression, StringExpression, and CorrelatedSubquery, The type can be determined using the java or groovy “instanceof” call. The ArrayExpression represents an expression owned by a mapping attribute, which is possibly a complex expression with referenced sub-expressions. The StringExpression represents a simple string expression. The CorrelatedQuery object represents a correlated query, also called as a scalar subquery, which is a subquery that returns a column and a row, that can be used as a query select item.

Example

The example prints out the type and text for each select item.

selectList = QUERY.getSelectList()
selectListString = ''
index = 0;
for (item in selectList) {
  if (selectListString.length() > 0) selectListString += ",\n"
  selectListString += sprintf('item %1$d: type=%2$s text=%3$s', index++, item.getClass().getName(), item.toString()) 
}

Select list string is:{#NL#} $[selectListString]

Select list string is:
item 0: type=oracle.odi.mapping.generation.ArrayExpression text=EMP_1.EMP_NO,
item 1: type=oracle.odi.mapping.generation.ArrayExpression text=EMP_1.LAST_NAME,
item 2: type=oracle.odi.mapping.generation.ArrayExpression text=EMP_1.FIRST_NAME,
item 3: type=oracle.odi.mapping.generation.ArrayExpression text=DEPT.DEPT_NAME 

Usage

public JoinTable getJoinTable ()

Description

This method is used to get the ANSI Join Table object from a FROM clause object, if the FROM clause object represents an ANSI join. The JoinTable object is a holder for the left and right join sources, which can be either simple tables or other join tables. It also holds the join type (inner, left outer, right outer, etc.), and the join condition expression.

Example

The example prints out the join type, right and left sources, and ON clause for the join table, if any.

joinTable = QUERY.getFromList().get(0).getJoinTable()
joinTableStr = ''
if (joinTable != null) {
  joinTableStr = sprintf('type=%1$s\nleft text=%2$s\nright text=%3$s\njoin condition=%4$s', 
    joinTable.getJoinType(), joinTable.getLeftText(), joinTable.getRightText(), joinTable.getPredicateText()) 
} else {
  joinTableStr = 'No join table found'
}

Join table information: $[joinTableStr]

Join table information:
type=INNER
left text=(
SELECT 
  EMP.EMP_NO AS EMP_NO ,
  EMP.LAST_NAME AS LAST_NAME ,
  EMP.FIRST_NAME AS FIRST_NAME ,
  EMP.DEPT_ID AS DEPT_ID   
FROM 
  ODI_SRC.EMP@NEXTGEN_TEST_ORACLE_SRC EMP  
GROUP BY
  EMP.EMP_NO,EMP.LAST_NAME,EMP.FIRST_NAME,EMP.DEPT_ID 
  ) EMP_1
right text=ODI_SRC.DEPT@NEXTGEN_TEST_ORACLE_SRC DEPT
join condition=EMP_1.DEPT_ID = DEPT.DEPT_ID 

Usage

public List<Table> getSourceTables ()

Description

This method is used to get a list of all the simple source tables that are included in the FROM clause object. It does not include Join tables or subquery references.

Example

The below example provides the table name and logical schema location for each simple source table in the mapping.

fromClause = QUERY.getFromList().get(0)
sourceTables = fromClause.getSourceTables()
sourceTableList = '';
context = physicalNode.getContext();
for (sourceTable in sourceTables) {
  sourceTableLocation = sourceTable.getBoundDatastore().getModel().getObjectLocation(context);
  sourceTableList += sprintf('Table name=%1$s, logical schema=%2$s\n', sourceTable.getName(), sourceTableLocation.getLogicalSchema().getName())
}

Source table list: $[sourceTableList]

Table name=EMP, logical schema=NEXTGEN_TEST_ORACLE_SRC
Table name=DEPT, logical schema=NEXTGEN_TEST_ORACLE_SRC 

Usage

public TableQueryReference getTableQuery ()
public TableQueryReference getLeftTableQueryRef ()
public TableQueryReference getRightTableQueryRef ()

Description

These methods are used to get the table query object for a FROM clause or from a JoinTable object that contains a table query. A table query is a holder for a subquery object or a simple table. Depending on the KM that is used, the TableQuery may be contained by the FROM clause itself, or on the right or left side of a JoinTable object.

Example

The example finds whether the first FROM clause has a JoinTable, and if so, gets the subquery from the left-hand side of the join table, if there is a subquery on the left side.

fromClause = QUERY.getFromList().get(0)
fromClauseHasJoinTable = (fromClause.getJoinTable() == null ? "does not have" : "has") + " a join table"
tableQueryRef = QUERY.getFromList().get(0).getJoinTable().getLeftTableQueryRef()
query = null
subqueryText = null
if (tableQueryRef != null) {  // Not every FROM clause has a table query object, so protect against null.
  query = tableQueryRef.getQuery()
  if (query != null) {
    subqueryText = query.getText()
  }
}

From clause $[fromClauseHasJoinTable]
{#IF $[subqueryText] != null #}
subquery text:
$[subqueryText]
{# ELSE #}
No subquery text found.
{# ENDIF #}

From clause has a join table subquery text:

SELECT 
  EMP.EMP_NO AS EMP_NO ,
  EMP.LAST_NAME AS LAST_NAME ,
  EMP.FIRST_NAME AS FIRST_NAME ,
  EMP.DEPT_ID AS DEPT_ID   
FROM 
  ODI_SRC.EMP@NEXTGEN_TEST_ORACLE_SRC EMP  
GROUP BY
  EMP.EMP_NO,EMP.LAST_NAME,EMP.FIRST_NAME,EMP.DEPT_ID 

Usage

public String getTemplate ()

Description

This method gets the code generation template for an ArrayExpression object. An ArrayExpression is a special code generation expression object that can handle nested expressions. For example, if an ODI mapping has multiple expression components connected one after the other, and the expressions for each one reference the expression attributes of the previous expression component, then there are nested expressions in the final query. For example, suppose the first expression EXPR has an attribute EMPSAL whose expression is “EMP.SAL + 100”, and then the next expression component has an expression like “EXPR.EMPSAL – 50”. In that case the final expression used in the extract query would be “(EMP.SAL + 100) – 50”. The ArrayExpression object handles this by having a top-level template, which has the template references to child objects in the template text. The template references in the text looks like this: “@{R0}”. This refers to a child expression whose key in the child hash table is “R0”. Each child object could be a simple String expression, or another ArrayExpression for multiple levels of nesting, or a source attribute. The getTemplate() method returns the textual template used to produce the ArrayExpression text.

Example

The example loops through all the select list items in the source query, finds the ones that are implemented as ArrayExpression objects, and displays the full text and the template text.

selectList = QUERY.getSelectList()
arrayExprListString = ''
for (selectItem in selectList) {
  ArrayExpression arrayExpr = selectItem.getArrayExpression()
  if (arrayExpr != null) {
    if (arrayExprListString.length() != 0) arrayExprListString += '\n';
    arrayExprListString += sprintf('text=%1$s, template=%2$s', arrayExpr.getText(), arrayExpr.getTemplate())
  }
}

text=(EMP.EMP_NO + 100)*2, template=(@{R0} + 100)*2
text=EMP.LAST_NAME, template=@{R0}
text=EMP.FIRST_NAME, template=@{R0}
text=(EMP.FIRST_NAME || EMP.LAST_NAME), template=@{R0}
text=DEPT.DEPT_NAME, template=@{R0} 

Usage

public Map<String,Object> getChildMap ()

Description

This method gets a hash map that contains the child objects that are owned by this ArrayExpression object. The hash key is the matching key used in the ArrayExpression template, as described in the previous method.

Example

The example loops through all the select list items in the source query, finds the ones that are implemented as ArrayExpression objects, and displays the full text and the template text.

selectList = QUERY.getSelectList()
arrayExprListString = ''
for (selectItem in selectList) {
  ArrayExpression arrayExpr = selectItem.getArrayExpression()
  if (arrayExpr != null) {
    if (arrayExprListString.length() != 0) arrayExprListString += '\n';
    arrayExprListString += sprintf('ArrayExpression text=%1$s, template=%2$s', arrayExpr.getText(), arrayExpr.getTemplate())
    childMap = arrayExpr.getChildMap()
    for (childKey in childMap.keySet()) {
      arrayExprListString += sprintf('\n\tChild item: key=%1$s, object=%2$s', childKey, childMap.get(childKey).toString())
    }
  }
} 

Here is the ArrayExpression list with child objects:$[arrayExprListString]

ArrayExpression text=(EMP.EMP_NO + 100)*2, template=(@{R0} + 100)*2
	Child item: key=R0, object=EMP.EMP_NO
ArrayExpression text=EMP.LAST_NAME, template=@{R0}
	Child item: key=R0, object=EMP.LAST_NAME
ArrayExpression text=EMP.FIRST_NAME, template=@{R0}
	Child item: key=R0, object=EMP.FIRST_NAME
ArrayExpression text=(EMP.FIRST_NAME || EMP.LAST_NAME), template=@{R0}
	Child item: key=R0, object=EMP.FIRST_NAME || EMP.LAST_NAME
ArrayExpression text=DEPT.DEPT_NAME, template=@{R0}
	Child item: key=R0, object=DEPT.DEPT_NAME