COLUMN & COL.nnn statements
(Extract-Phase Column Logic)

TABLE OF CONTENTS

  1. How do I use COLUMN & COL.nnn statements in ECL?
  2. Syntax
  3. Examples: COLUMN & COL.nnn (ECL)
  4. Examples: IF with COLUMN & COL.nnn (ECL)

How do I use COLUMN & COL.nnn statements in ECL?

The COLUMN statement sets the value of the current column. A COLUMN statement is allowed in Extract Column Logic.

COL.nnn refers to a column value where nnn is the number of that column in the current view (starting at one on the leftmost column).

You can set the value of a column in the view using a COL.nnn statement in Extract Column Logic. This is the only logic text where COL.nnn is a statement. You can also use COL.nnn in a query, for example “IF (COL.nnn = ? “ ).

(Syntax Legend)

Syntax

Function COLUMN ECL

Function COL.nnn ECL

Note: <Expression> is defined under IF statements

Rules for the syntax

You can set the value of previous columns in the view using the COL.nnn statement. This statement can be placed in the Extract Column Logic text for any column. This means that any column can set the value of any other column of a lower column number.

You can only set the value of the current column using the COLUMN statement.

See also topic: Rules for all Logic Text

Examples: COLUMN & COL.nnn (ECL)

In all the following examples, COLUMN can be replaced by COL.nnn, for example COL.3. You can set the value of any COL.nnn from any other column. You can create multiple COL.nnn statements in Extract Column Logic text.

Example logic text Meaning
COLUMN = ({field2}/{field1}) * 100
COL.27 = {field1} * {field26}
COL.28 = {field14} + {field1}
COL.29 = 0
COL.30 = “ABC”		 Set current column to field2 divided by field1
all multiplied by 100.
Set column 27 to field1 times field26.
Set column 28 to field14 plus field1.
Set column 29 to zero.
Set column 30 to “ABC”.
COLUMN = ALL(“-“) Set current column to all dashes.
COLUMN = REPEAT(“-“, 13) Set current column to 13 dashes.
COLUMN = “\xFF” Set current column to hexadecimal FF.
COLUMN = {Lookup1.Field3} Set current column to Field3 found by
lookup path Lookup1
COLUMN = {Lookup1.Field3,field7} Set current column to Field3 found by
lookup path Lookup1 using
effective date of field7.
COLUMN = {Lookup1.Field3,;$SYM=”A”} Set current column to Field3 found by
lookup path Lookup1 using
symbol SYM set to “A”.
COLUMN = {Lookup1.Field3,field7;$SYM1=3,$SYM2=0} Set current column to Field3 found by
lookup path Lookup1 using
effective date of field7 and symbols
SYM1 set to 3 and SYM2 set to zero.
COLUMN = DAYSBETWEEN({BUY_DATE},{SHIP_DATE}) Set current column to the days between
the transaction date and the shipping date.

Examples: IF with COLUMN & COL.nnn (ECL)

In all the following examples, COLUMN can be replaced by COL.nnn, for example COL.3. You can set the value of any COL.nnn from any other column. You can create multiple IF statements in Extract Column Logic text. However, you cannot inquire on COL.nnn (for example, IF COL.4 = 0 is not allowed).

Example logic text Meaning
IF ({field1} > 0) THEN
    COLUMN = ({field2}/{field1}) * 100
    COL.27 = {field1} * {field26}
    COL.28 = {field14} + {field1}
ELSE
    COLUMN = 0
    COL.27 = 0
    COL.28 = 0
ENDIF		 If field1 is greater than zero then set current column
to field2 divided by field1 all multiplied by 100,
set column 27 to field1 times field26
and set column 28 to field 14 plus field1.
If field1 is not greater than zero then set
current column and columns 27 and 28 to zero.
IF (CURRENT({field5}) <> PRIOR({field5}))
    THEN COLUMN = “PRODUCT: “
    ELSE COLUMN = “ “
ENDIF		 If current record field5 has a different value
from the previous record,
set current column to “PRODUCT: “
otherwise set current column to blank.
This assumes the input file is sorted into field5 order.
IF ({field5} = “Total”)
    THEN COLUMN = ALL(“-“)
ENDIF		 If field5 is “Total” then set current column
to all dashes.
IF COL.01 = ALL(“-“)
    THEN COLUMN = {field2} + {field3}
ENDIF		 If column 1 is all dashes, then set current column
to a total of fields 2 and 3.
IF ({field5} = “Total”)
    THEN COLUMN = REPEAT(“-“, 13)
ENDIF		 If field5 is “Total” then set current column
to 13 dashes.
IF ({field6} = REPEAT(“-“, 13))
    THEN COLUMN = {field2} + {field3}
ENDIF		 If field6 is 13 dashes, then set current column
to a total of fields 2 and 3.
IF ({field5} = “Total”)
    THEN COLUMN = “\xFF”
ENDIF		 If field5 is “Total” then set current column
to hexadecimal FF.
IF ({field6} = “\xFF”)
    THEN COLUMN = {field2} + {field3}
ENDIF		 If field6 is hexadecimal FF, then set current column
to a total of fields 2 and 3.
IF ISNOTSPACES({field1})
    THEN COLUMN = {field1}
    ELSE COLUMN = “DEFAULT”
ENDIF		 If field1 is not spaces then set current column
to field1, otherwise set current column to “DEFAULT”.
IF ISFOUND({Lookup1})
    THEN COLUMN = {Lookup1}
    ELSE COLUMN = “ “
ENDIF		 If the lookup path Lookup1 uses current record
to successfully find a target record,
then set current column to lookup path field found,
otherwise set current column to blank.
IF ISFOUND({Lookup2;$SYM=”A”})
    THEN COLUMN = {Lookup2;$SYM=”A”}
    ELSE COLUMN = 0
ENDIF		 If lookup path Lookup2 with symbol SYM “A” works,
then set current column to that lookup field,
otherwise set current column to zero.
IF ISNULL({field4}
    THEN COLUMN = “EMPTY”
    ELSE COLUMN = {field4}
ENDIF		 If field4 for current record has null values,
then set current column to “EMPTY”,
otherwise set current column to field4.
IF ISNUMERIC({field4}
    THEN COLUMN = {field4} * 100
    ELSE COLUMN = 0
ENDIF		 If field4 for current record is numeric,
then set current column to field4 times 100,
otherwise set current column to zero.
IF (DAYSBETWEEN({BUY_DATE},{SHIP_DATE}) > 10)
    THEN COLUMN = {SHIP_DATE}
    ELSE COLUMN = {BUY_DATE}
ENDIF		 If there are more than 10 days between the
transaction date and the shipping date,
then set current column to the shipping date,
otherwise set current column to the transaction date.
IF ({field1} BEGINS_WITH “BBB”)
    THEN COLUMN = {field1}
    ELSE COLUMN = “ “
ENDIF		 If field1 begins with characters “BBB”
then set current column to field1,
otherwise set current column to blank.
IF ({field2} CONTAINS “CCC”)
    THEN COLUMN = {field2}
    ELSE COLUMN = “ “
ENDIF		 If field2 contains characters “CCC”
then set current column to field2,
otherwise set current column to blank.
IF ({field3} ENDS_WITH “EEE”)
    THEN COLUMN = {field3}
    ELSE COLUMN = “ “
ENDIF		 If field3 ends with characters “EEE”
then set current column to field3,
otherwise set current column to blank.