Problem with EXTERNAL TABLE

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

Mark: Remove the "RECORDEND '|' " parameter from the external table definition. It is causing all six rows to be appended to each other in a single line in the file. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Really just a quick guess, but shouldn't RECORDEND and (column) DELIMITER be different? ------------------------------ Andreas Legner ------------------------------

Thanks Art. That fixed it. ------------------------------ Mark Collins ------------------------------

Thanks. You and Art both spotted that problem. ------------------------------ Mark Collins ------------------------------

Mark, I can see you create ext table does not feature à reject file that may report you errors at the row level. Did you try this? Obtenir Outlook pour Android

I generally do include a REJECTFILE when we're using an EXTERNAL TABLE for loading data from other systems, since you can't always control what the inbound file might contain. In this case, since the purpose is unloading data, we opted to omit that. ------------------------------ Mark Collins ------------------------------