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
- Create a file that contains only a newline character.
echo "" > /tmp/cr.fixed
- Create an internal table to store this newline value to
use when you unload the data.
CREATE TABLE dummyCr (cr CHAR(1));
- 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');
- 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:
- 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');
- 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;