getColList() Method

Usage

public java.lang.String getColList(
java.lang.String pStart, 
java.lang.String pPattern, 
java.lang.String pSeparator, 
java.lang.String pEnd, 
java.lang.String pSelector)

Alternative syntaxes:

public java.lang.String getColList(
java.lang.String pStart, 
java.lang.String pPattern, 
java.lang.String pSeparator, 
java.lang.String pEnd)

public java.lang.String getColList(
java.lang.String pPattern, 
java.lang.String pSeparator,
java.lang.String pSelector)

public java.lang.String getColList(
java.lang.String pPattern, 
java.lang.String pSeparator)

Description

Offers a list of columns and expressions. The columns list depends on the phase during which this method is called.

The pPattern parameter is interpreted and then repeated for each element of the list (selected according to pSelector parameter) and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd.

In the alternative syntax, any parameters not set are set to an empty string.

Loading (LKM)

All mapping expressions that are executed in the current source environment as well as the columns used in the mapping, filters expressions, joins that are executed in the staging area. 

Only the mappings tagged as "execute" in the interface are considered.

If there is a journalized datastore in the source of the interface, the three journalizing pseudo columns JRN_FLG, JRN_DATE,and JRN_SUBSCRIBER are added as columns of the journalized source datastore.

Integration (IKM)

All current mapping expressions tagged as "execute" in the current interface.

The list contains one element for each column that is loaded (tagged as "execute") in the target table of the current interface.

If there is a journalized datastore in the source of the interface, and it is located in the staging area, the three journalizing pseudo columns JRN_FLG, JRN_DATE,and JRN_SUBSCRIBER are added as columns of the journalized source datastore.

Check (CKM)

All the columns of the target table (with static or flow control)

To distinguish the columns of the target table from those filled in the current interface, you must use the MAP selector.

Actions

All the columns of the table handles by the DDL command.

In the case of modified, added or deleted columns, the NEW and OLD selectors are used to retrieve either the new version of the old version of the modified column being processed by the DDL command.

Parameters

Parameter

Type

Description

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of the attributes usable in a pattern is detailed in the table « Pattern Attributes List »

Each occurrence of the attributes in the pattern string is replaced by its value. Attributes must be between brackets ([ and ])

Example « My string [COL_NAME] is a column »

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.

pSelector

String

String that designates a Boolean expression that allows to filter the elements of the initial list with the following format :

<SELECTOR> <Operator> <SELECTOR> etc. Parenthesis are authorized.

Authorized operators:

  1. No: NOT or !

  2. Or: OR or ||

  3. And: AND or &&

Example: (INS AND UPD) OR TRG

The description of valid selectors is provided in the table « Selectors Description  »

Pattern Attributes List

The following table lists different parameters values as well as their associated description.

Parameter value

Description

I_COL

Internal identifier of the column

COL_NAME

Name of the column

COL_HEADING

Header of the column

COL_DESC

Description of the column

POS

Position of the column

LONGC

Column length (Precision)

SCALE

Scale of the column

FILE_POS

Beginning (index) of the column

BYTES

Number of physical bytes in the column

FILE_END_POS

End of the column (FILE_POS + BYTES)

IND_WRITE

Write right flag of the column

COL_MANDATORY

Mandatory character of the column (0: null authorized, 1: not null)

CHECK_FLOW

Flow control flag of the column (0: do not check, 1: check)

CHECK_STAT

Static control flag of the column (0: do not check, 1: check)

COL_FORMAT

Logical format of the column

COL_DEC_SEP

Decimal symbol of the column

REC_CODE_LIST

List of the record codes retained in the column

COL_NULL_IF_ERR

Processing flag of the column (0 = Reject, 1 = Set to null active trace, 2= set to null inactive trace)

DEF_VALUE

Default value of the column

EXPRESSION

Text of the expression executed on the source (expression as typed in the mapping or column name making an expression executed on the staging area).

CX_COL_NAME

Computed name of the column used as a container for the current expression on the staging area

ALIAS_SEP

Separator used for the alias (from the technology)

SOURCE_DT

Code of the column's datatype.

SOURCE_CRE_DT

Create table syntax for the column's datatype.

SOURCE_WRI_DT

Create table syntax for the column's writable datatype.

DEST_DT

Code of the column's datatype converted to a datatype on the target technology.

DEST_CRE_DT

Create table syntax for the column's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the column's writable datatype converted to a datatype on the target technology.

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this column in the data model.

MANDATORY_CLAUSE

Returns NOT NULL is the column is mandatory. Otherwise, returns the null keyword  for the technology.

DEFAULT_CLAUSE

Returns DEFAULT <default value> if any default value exists. Otherwise, returns and empty string.

COL_DESC

Description (comment) of the column. Quotes and double quotes are replaced with spaces.

JDBC_TYPE

Data Services - JDBC Type of the column returned by the driver.

<flexfield code>

Flexfield value for the current column.

Selectors Description

Parameters value

Description

INS

  • LKM: Not applicable (*)

  • IKM: Only for mapping expressions marked with insertion

  • CKM: Not applicable

UPD

  • LKM: Not applicable (*)

  • IKM: Only for the mapping expressions marked with update

  • CKM: Non applicable

TRG

  • LKM: Not applicable (*)

  • IKM: Only for the mapping expressions executed on the target

  • CKM: Mapping expressions executed on the target.

NULL

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading not nullable columns

  • CKM: All target columns that do not accept null values

PK

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading the primary key columns

  • CKM: All the target columns that are part of the primary key

UK

  • LKM: Not applicable (*)

  • IKM: All the mapping expressions loading the update key column chosen for the current interface.

  • CKM: Not applicable.

REW

  • LKM: Not applicable (*)

  • IKM: All the mapping expressions loading the columns with read only flag not selected.

  • CKM: All the target columns with read only flag not selected.

UD1

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading the columns marked UD1

  • CKM: Not applicable

UD2

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading the columns marked UD2

  • CKM: Not applicable

UD3

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading the columns marked UD3

  • CKM: Not applicable

UD4

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading the columns marked UD4

  • CKM: Not applicable

UD5

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading the columns marked UD5

  • CKM: Not applicable

MAP

  • LKM: Not applicable

  • IKM: Not applicable

  • CKM: 

    Flow control: All columns of the target table loaded with expressions in the current interface

    Static control: All columns of the target table

SCD_SK

  • LKM, CKM, IKM: All columns marked SCD Behavior: Surrogate Key in the data model definition.

SCD_NK

  • LKM, CKM, IKM: All columns marked SCD Behavior: Natural Key in the data model definition.

SCD_UPD

  • LKM, CKM, IKM: All columns marked SCD Behavior: Overwrite on Change in the data model definition.

SCD_INS

  • LKM, CKM, IKM: All columns marked SCD Behavior: Add Row on Change in the data model definition.

SCD_FLAG

  • LKM, CKM, IKM: All columns marked SCD Behavior: Current Record Flag in the data model definition.

SCD_START

  • LKM, CKM, IKM: All columns marked SCD Behavior: Starting Timestamp in the data model definition.

SCD_END

  • LKM, CKM, IKM: All columns marked SCD Behavior: Ending Timestamp in the data model definition.

NEW

  • Actions: the column added to a table, the new version of the modified column of a table.

OLD

  • Actions: The column dropped from a table, the old version of the modified column of a table.

WS_INS

  • SKM: The column is flagged as allowing INSERT using Data Services.

WS_UPD

  • SKM: The column is flagged as allowing UDATE using Data Services.

WS_SEL

  • SKM: The column is flagged as allowing SELECT using Data Services.

(*) Important Note : Using certain selectors in a LKM - indicated with a * -  is possible but not recommended. Only columns mapped on the source in the interface are returned. As a consequence, the result could be incorrect depending on the interface.
For example, for the UK selector, the columns of the key that are not mapped or that are not executed on the source will not be returned with the selector.

Examples

If the CUSTOMER table contains the columns (CUST_ID, CUST_NAME, AGE) and we want to generate the following code:
create table CUSTOMER (CUST_ID numeric(10) null, CUST_NAME varchar(50) null, AGE numeric(3) null)

we just need to write:

create table CUSTOMER <%=odiRef.getColList("(", "[COL_NAME] [SOURCE_CRE_DT] null", ", ", ")", "")%>

 

Explanation: the getColList function will be used to generate (CUST_ID numeric(10) null, CUST_NAME varchar(50) null, AGE numeric(3) null). It will start and end with a parenthesis and repeat a pattern (column, data type, and null) separated by commas for each column. Thus,