COLUMN CLPPlus command

The COLUMN CLPPlus command specifies character and numeric output formats for columns in a table. Formats set by the COLUMN command remain in effect only for the duration of the current session. You can change or clear format settings for the same column more than once in the current session.

When you issue COLUMN for a specified column in a database, format settings are by default displayed using the standard output of the CLPPlus interface.

Invocation

This command must be executed from the CLPPlus interface.

Authorization

None

Required connection

None

Command syntax

Command parameters

column

Specifies the name of a table column to which formats are applied. If you do not specify any parameters after column, default format settings apply. If you do not specify any parameters after column, and you have set parameters for the same column previously in this session, the last set of parameters assigned to the column are again used.

ALIAS alias-name

Specifies and alias name for column. The maximum length of alias-name is 255 characters. The alias can be used in other commands such as: COMMAND, COMPUTE, and COLUMN.

CLEAR | CLE

Changes all formatting settings for the specified column to their default values. If you specify CLEAR, you cannot specify any parameters other than column.

FORMAT | FOR spec

Specifies the formats to apply to the specified column. There are two types of columns: character columns and numeric columns.

For a character column, the value of spec is An, where n specifies the number of characters that can be used to display the column. The data wraps if it is wider than the specified width.

For numeric columns, the value of spec can be one or more of the following characters:

Displays a leading dollar sign.

Displays a comma at the indicated position.

Displays a decimal point at the indicated position.

Displays a zero at the indicated position.

Displays a significant digit at the indicated position.

If loss of significant digits occurs due to overflow of the format settings, the # character will be displayed.

HEADING | HEA text

Specifies a heading for the specified column.

FOLD_BEFORE

Before printing the values for the specified column, new line feed and carriage return are provided for each row.

FOLD_AFTER

After printing the values for the specified column, new line feed and carriage return are provided for each row.

LIKE source-column

The format and display attributes of source-column are applied to column.

NEWLINE

A synonym of FOLD_AFTER. After printing the values for the specified column, new line feed and carriage return are provided for each row.

NEW_VALUE variable_name

Defines a variable that can hold the new value for a break column defined using the BREAK command. The variable can be used with page top title TTITLE command. The break column must be defined with the SKIP PAGE action.

The NEW_VALUE command can also be used in all places within the current session. Similar to a substitution variable. Whenever you define a NEW_VALUE variable for a column, CLPPlus creates a substitution variable with the specified variable name. This variable is updated with the column value on each column break.

NULL text

When the value for the specified column is NULL, the value specified for text is printed. The maximum length of text is 255 characters.

OLD_VALUE variable_name

Defines a variable that can hold the old value for a break column defined using the BREAK command. The variable can be used with page bottom title BTITLE command. The break column must be defined with the SKIP PAGE action.

The OLD_VALUE command can also be used in all places within the current session. Similar to a substitution variable. Whenever you define a OLD_VALUE variable for a column, CLPPlus creates a substitution variable with the specified variable name. This variable is updated with the column value on each column break.

PRINT | NOPRINT

Specifies whether console printing of a specified column is enabled or disabled.

WRAPPED | TRUNCATED

Specifies if column data is wrapped or truncated in the CLPPlus output if it exceeds the specified format.

JUSTIFY [LEFT | RIGHT | CENTER]

Specifies column justification to be either LEFT, RIGHT, or CENTER.

OFF

Changes the formatting options to the default values. The values that you previously specified for the column in the session are saved and still available for use later in the session.

Changes the formatting options to the values applied to the specified column the last time that you ran COLUMN.

Examples

In the following example, the SET PAGESIZE command sets the maximum page length to 9999, and the COLUMN command changes the display width of the JOB column to five characters. The SELECT statement then prints specified columns in the table.

SQL> SET PAGESIZE 9999
SQL> COLUMN JOB FORMAT A5
SQL> COLUMN JOB
COLUMN     JOB     ON
FORMAT     A5
WRAPPED
SQL> SELECT EMPNO, ENAME, JOB FROM EMP;

EMPNO   ENAME       JOB
------  ----------  -----
  7369  SMITH       CLERK
  7499  ALLEN       SALES
                    MAN
  7521  WARD        SALES
                    MAN
  7566  JONES       MANAG
                    ER
  7654  MARTING     SALES
                    MAN
  7698  BLAKE       MANAG
                    ER
  7782  CLARK       MANAG
                    ER
  7788  SCOTT       ANALY
                    ST
  7839  KING        PRESI
                    DENT
  7844  TURNER      SALES
                    MAN
  7876  ADAMS       CLERK
  7900  JAMES       CLERK
  7902  FORD        ANALY
                    ST
  7934  MILLER      CLERK

14 rows received.

In the following example, the COLUMN command applies a numeric format to the SAL column:

SQL> COLUMN SAL FORMAT $99,999.00
SQL> COLUMN
COLUMN   JOB  ON
FORMAT   A5
WRAPPED

COLUMN   SAL  ON
FORMAT   $99,999.00
WRAPPED
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP;

EMPNO ENAME      JOB           SAL
----- ---------- ----- -----------
 7369 SMITH      CLERK     $800.00
 7499 ALLEN      SALES   $1,600.00
                 MAN

 7521 WARD       SALES   $1,250.00
                 MAN

 7566 JONES      MANAG   $2,975.00
                 ER

 7654 MARTIN     SALES   $1,250.00
                 MAN

 7698 BLAKE      MANAG   $2,850.00
                 ER

 7782 CLARK      MANAG   $2,450.00
                 ER

 7788 SCOTT      ANALY   $3,000.00
                 ST

 7839 KING       PRESI   $5,000.00
                 DENT

 7844 TURNER     SALES   $1,500.00
                 MAN

 7876 ADAMS      CLERK   $1,100.00
 7900 JAMES      CLERK     $950.00
 7902 FORD       ANALY   $3,000.00
                 ST

 7934 MILLER     CLERK   $1,300.00

14 rows retrieved.

In the following example, the improved NEW_VALUE parameter behavior is shown. The new OLD_VALUE behavior is identical:

SQL> break on empno skip 1
SQL> column empno new_value highest_sal
SQL> select empno from employee order by salary;

EMPNO 
------
200340
******

000290
******

200330
******

000310
******

      ...
      ...

000070
******

000030
******

000010
******	

SQL>DEFINE
DEFINE HIGHEST_SAL = 000010

SQL> select EMPNO, FIRSTNME, MIDINIT, LASTNAME from employee where empno=&highest_sal;
EMPNO  FIRSTNME     MIDINIT LASTNAME
------ ------------ ------- ---------------
000010 CHRISTINE    I       HAAS