IBM PureData System for Analytics, Version 7.1

Decimal delimiter examples

The following are examples of how to use the decimal delimiter option. The option use is shown in bold text.

For text-delimited format for the table level:

INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(delim 'delim' decimalDelim ',');

For fixed-length format for the table level:

INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(decimalDelim ',' format 'fixed' layout (c1 int bytes 4, c2 float bytes 
6, c3 numeric(10,2) bytes 11, c4 time 24HOUR ':' bytes 11 );

For fixed-length format for the column level.

For numeric data type:

   INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(format 'fixed' layout (c1 int bytes 4 , c2 float bytes 6, c3 
numeric(10,2) decimal ',' bytes 11) );
   INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(format 'fixed' layout (c1 int bytes 4 , c2 float bytes 6, c3 
numeric(10,2) decimal decimalDelim ',' bytes 11) );

For float data type:

   INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(format 'fixed' layout (c1 int bytes 4 , c2 float floating ',' 
bytes 6, c3 numeric(10,2) bytes 11) );
   INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(format 'fixed' layout (c1 int bytes 4 , c2 float floating 
decimalDelim ',' bytes 6, c3 numeric(10,2) bytes 11) );

For double data type:

   INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(format 'fixed' layout (c1 int bytes 4 , c2 double exponential ',' 
bytes 6, c3 numeric(10,2) bytes 11) );
   INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(format 'fixed' layout (c1 int bytes 4 , c2 float exponential 
decimalDelim ',' bytes 6, c3 numeric(10,2) bytes 11) );

For time data types (time, timetz, timestamp):

   INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(format 'fixed' layout (c1 int bytes 4 , c2 time 12HOUR 
decimalDelim ',' bytes 12, c3 numeric(10,2) bytes 11) );
   INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(format 'fixed' layout (c1 int bytes 4 , c2 time timeDelim '-' 
decimalDelim ',' bytes 12, c3 numeric(10,2) bytes 11) );
   INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(format 'fixed' layout (c1 int bytes 4 , c2 time timeDelim '-' ',' 
bytes 12, c3 numeric(10,2) bytes 11) );
   INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(format 'fixed' layout (c1 int bytes 4 , c2 time 12HOUR '-' 
decimalDelim ',' bytes 12, c3 numeric(10,2) bytes 11) );
   INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(format 'fixed' layout (c1 int bytes 4 , c2 time 12HOUR '-' ',' 
bytes 12, c3 numeric(10,2) bytes 11) );
   INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(format 'fixed' layout (c1 int bytes 4 , c2 time 12HOUR timeDelim 
'-' decimalDelim ',' bytes 12, c3 numeric(10,2) bytes 11) );
   INSERT INTO <target-table> SELECT * FROM '<external-table>' USING 
(format 'fixed' layout (c1 int bytes 4 , c2 time 12HOUR timeDelim 
'-' ',' bytes 12, c3 numeric(10,2) bytes 11) )