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;