Adding a Newline Field in a SELECT Statement

You can use an external table to load the newline character in your internal table.

About this task

To add an end-of-line character, select a final value from a table that contains a newline character, as in the following example:

Procedure

  1. Create a file that contains only a newline character. 
    echo "" > /tmp/cr.fixed
  2. Create an internal table to store this newline value to use when you unload the data. 
    CREATE TABLE dummyCr (cr CHAR(1));
  3. Create the external table to load the newline value. 
    CREATE EXTERNAL TABLE x_cr (cr CHAR(1) EXTERNAL CHAR(1)) 
    USING (DATAFILES ("DISK:/tmp/cr.fixed"), FORMAT 'FIXED');
  4. Load the external table in the internal dummyCr table. 
    INSERT INTO dummyCr SELECT * FROM x_cr;

Results

The internal table, dummyCr, now contains an end-of-line character that you can use to unload in a SELECT statement:

  1. To unload data from your internal table to an external table, create the external table with the end-of-line character as an EXTERNAL CHAR. 
    CREATE EXTERNAL TABLE sample_ext 
(
   lastname CHAR(10) EXTERNAL CHAR(10),
   firstname CHAR(10) EXTERNAL CHAR(10),
   dateofbirth DATE EXTERNAL CHAR(12),
   eol CHAR(1) EXTERNAL CHAR(1)) 
USING (DATAFILES ....), FORMAT 'FIXED');
  2. Select from the internal table and the dummyCr table to create an output file that has rows separated by end-of-line characters. 
    INSERT INTO sample_ext(lastname, firstname, dateofbirth, eol)
SELECT a.lastname, a.firstname, a.dateofbirth, b.cr
FROM mytable a, dummyCr b;