sqlnetezzaexternal-tables

Header row missing text while exporting to a file using CREATE EXTERNAL TABLE in Netezza

After the extract, the file is missing the header column text. It adds and empty line with delimiter. It only happens when I use an alias name with a function in Netezza. I have seen some solutions using union all, but is there any less resource intensive way to fix this.

CREATE EXTERNAL TABLE 'c:\temp\test.csv'
USING
(
 IncludeHeader 
 ENCODING 'internal'
 ESCAPECHAR '\'
 DELIMITER ','
 REMOTESOURCE 'JDBC'
)
AS 
SELECT a.COL1,a.COL2
FROM (
 select quote_ident(COL1) as COL1,
 quote_ident(COL2)  as COL2
 FROM tablename
 ) a;

Output

Solution 
  • If your SQL looks like this

create external table '/tmp/data.out' using (includeheader true) as
select name as "Customer Name", addr as "Customer Address" from customer_table;

Add the following 2 bits of uppercase text to it

create external table '/tmp/data.out' using (includeheader true) as
SELECT * FROM (
select name as "Customer Name", addr as "Customer Address" from customer_table
LIMIT ALL) SUB1;