Informix 14.10.FC7W1, RHEL 8.5 (I think)
I have a table invoice_tbl, and I want to unload some of the data from that table to an external table. The table has six rows that meet the selection criteria, but the resulting file contains only one row.
I have the following SQL:
SELECT count(*)
FROM invoice_tbl
WHERE batchno = 148;
DROP TABLE IF EXISTS ext_invoice_table;
--
-- the above query returns a result of 6
--
CREATE EXTERNAL TABLE ext_invoice_table
( id INTEGER
, region SMALLINT
, invoiceno INTEGER
, invdat DATE
, firstname VARCHAR(20)
, midname VARCHAR(20)
, lastname VARCHAR(20)
, suffix CHAR(3)
, add1 VARCHAR(30)
, add2 VARCHAR(30)
, city VARCHAR(20)
, state CHAR(2)
, zip CHAR(10)
, totamt MONEY(16,2)
)
USING( FORMAT 'DELIMITED'
, DELIMITER '|'
, RECORDEND '|'
, DATAFILES('DISK:/tmp/ext_tab_invoice')
);
INSERT INTO ext_invoice_table
SELECT id
, region
, invoiceno
, invdat
, upper(firstname) AS firstname
, upper(midname) AS midname
, upper(lastname) AS lastname
, upper(suffix) AS suffix
, upper(add1) AS add1
, upper(add2) AS add2
, upper(city) AS city
, upper(state) AS state
, zip
, totamt
FROM invoice_tbl
WHERE batchno = 148
ORDER BY region, invoiceno
INTO EXTERNAL ext_invoice_table;
SELECT dbinfo('sqlca.sqlerrd2') as rows_processed FROM sysmaster:sysdual;
--
-- the above query also returns a result of 6
--
However, as I said earlier, the actual file /tmp/ext_tab_invoice has just one row. It appears to be the first row, based on the invoiceno.
I've also tried it as:
SELECT count(*)
FROM invoice_tbl
WHERE batchno = 148;
DROP TABLE IF EXISTS ext_invoice_table;
SELECT id
, region
, invoiceno
, invdat
, upper(firstname) AS firstname
, upper(midname) AS midname
, upper(lastname) AS lastname
, upper(suffix) AS suffix
, upper(add1) AS add1
, upper(add2) AS add2
, upper(city) AS city
, upper(state) AS state
, zip
, totamt
FROM invoice_tbl
WHERE batchno = 148
ORDER BY region, invoiceno
INTO EXTERNAL ext_invoice_table
USING( FORMAT 'DELIMITED'
, DELIMITER '|'
, RECORDEND '|'
, DATAFILES('DISK:/tmp/ext_tab_invoice')
)
;
SELECT dbinfo('sqlca.sqlerrd2') as rows_processed FROM sysmaster:sysdual;
Again, dbinfo() shows 6 rows processed, but there is only one row in the file /tmp/ext_tab_invoice.
I know that I ran into a situation in 11.50 where if you had two separate INSERT INTO statements, the second one overwrote the results of the first, rather than appending. But this is a single INSERT statement, so all of the rows in the result set should be included in the output file.
Any recommendations before I open a ticket w/IBM?
------------------------------
Mark Collins
------------------------------
#Informix