| Latest versions | fixlist |
| 14.10.xC10 |
 |
| 12.10.xC16.X5 |
|
| 11.70.xC9.XB |
|
| 11.50.xC9.X2 |
|
| 11.10.xC3.W5 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
Informix - Problem description
| Problem IT33136 | Status: Closed |
SELECT INTO EXTERNAL TABLE USING DERIVED TABLE AND ORDER BY SEEMS TO CORRUPT OUTPUT | |
| product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
| Problem description: | |
Using the following example:
CREATE EXTERNAL TABLE tstbill
(
as_of_date date,
remit_to_name char(50),
remit_to_addr2 char(50),
remit_to_addr3 char(50),
remit_to_addr4 char(50),
remit_to_city char(50),
remit_to_state char(3),
remit_to_zip char(12)
)
USING
(DATAFILES
(
"DISK:/some/path/to/output/tstbill.txt"
)
);
INSERT INTO tstbill
select
today ,
'Associated Integrated Supply Chain Solutions',
'7954 Solution Center',
'0',
'0',
'Chicago' ,
'IL' ,
'60677-7009'
From
(
SELECT
today ,
'TEST' ,
'' ,
'' ,
'' ,
'Chicago' ,
'IL' ,
'60677-7009'
FROM
gtr gtr
WHERE
gtr.csc_id between '124360' AND '124360' AND
gtr.gla_id in ('14000')
order by 3,2
)
order by 1
-- if you comment out the above order by 1, the data in the
external
-- table no longer looks corrupted
If you have the "order by 1" in the select statement, when you
look at the data generated in the tstbill.txt, you will see
output like the following:
06/09/2020|Associated Integrated Supply Chain Solutions7954
S|olution Center00Ch
icagoIL60677-7009
06/09/2020|Associated Integrated Supply Chain Solutions7954
S|olution Center00Ch
icagoIL60677-7009
06/09/2020|Associated Integrated Supply Chain Solutions7954
S|olution Center00Ch
icagoIL60677-7009
06/09/2020|Associated Integrated Supply Chain Solutions7954
S|olution Center00Ch
icagoIL60677-7009
06/09/2020|Associated Integrated Supply Chain Solutions7954
S|olution Center00Ch
icagoIL60677-7009
06/09/2020|Associated Integrated Supply Chain Solutions7954
S|olution Center00Ch
icagoIL60677-7009
...
If the "order by 1" is removed from the select (so the order by
on the derived table) the output looks correct. Like the
following:
06/09/2020|Associated Integrated Supply Chain Solutions|7954
Solution Center|0|0
|Chicago|IL|60677-7009|
06/09/2020|Associated Integrated Supply Chain Solutions|7954
Solution Center|0|0
|Chicago|IL|60677-7009|
06/09/2020|Associated Integrated Supply Chain Solutions|7954
Solution Center|0|0
|Chicago|IL|60677-7009|
06/09/2020|Associated Integrated Supply Chain Solutions|7954
Solution Center|0|0
|Chicago|IL|60677-7009| | |
| Problem Summary: | |
****************************************************************
* USERS AFFECTED: *
* Users of Informix Server prior to 12.10.xC15 and 14.10.xC5. *
****************************************************************
* PROBLEM DESCRIPTION: *
* Using the following example: *
* *
* CREATE EXTERNAL TABLE tstbill *
* ( *
* as_of_date date, *
* remit_to_name char(50), *
* remit_to_addr2 char(50), *
* remit_to_addr3 char(50), *
* remit_to_addr4 char(50), *
* remit_to_city char(50), *
* remit_to_state char(3), *
* remit_to_zip char(12) *
* ) *
* *
* USING *
* (DATAFILES *
* ( *
* "DISK:/some/path/to/output/tstbill.txt" *
* ) *
* ); *
* *
* INSERT INTO tstbill *
* *
* select *
* today , *
* 'Associated Integrated Supply Chain Solutions', *
* '7954 Solution Center', *
* '0', *
* '0', *
* 'Chicago' , *
* 'IL' , *
* '60677-7009' *
* *
* From *
* ( *
* SELECT *
* today , *
* 'TEST' , *
* '' , *
* '' , *
* '' , *
* 'Chicago' , *
* 'IL' , *
* '60677-7009' *
* FROM *
* gtr gtr *
* WHERE *
* gtr.csc_id between '124360' AND '124360' AND *
* gtr.gla_id in ('14000') *
* order by 3,2 *
* ) *
* order by 1 *
* -- if you comment out the above order by 1, the data in the *
* external *
* -- table no longer looks corrupted *
* *
* If you have the "order by 1" in the select statement, when *
* you *
* look at the data generated in the tstbill.txt, you will see *
* output like the following: *
* *
* 06/09/2020|Associated Integrated Supply Chain Solutions7954 *
* S|olution Center00Ch *
* icagoIL60677-7009 *
* 06/09/2020|Associated Integrated Supply Chain Solutions7954 *
* S|olution Center00Ch *
* icagoIL60677-7009 *
* 06/09/2020|Associated Integrated Supply Chain Solutions7954 *
* S|olution Center00Ch *
* icagoIL60677-7009 *
* 06/09/2020|Associated Integrated Supply Chain Solutions7954 *
* S|olution Center00Ch *
* icagoIL60677-7009 *
* 06/09/2020|Associated Integrated Supply Chain Solutions7954 *
* S|olution Center00Ch *
* icagoIL60677-7009 *
* 06/09/2020|Associated Integrated Supply Chain Solutions7954 *
* S|olution Center00Ch *
* icagoIL60677-7009 *
* ... *
* *
* If the "order by 1" is removed from the select (so the order *
* by *
* on the derived table) the output looks correct. Like the *
* following: *
* *
* 06/09/2020|Associated Integrated Supply Chain Solutions|7954 *
* Solution Center|0|0 *
* |Chicago|IL|60677-7009| *
* 06/09/2020|Associated Integrated Supply Chain Solutions|7954 *
* Solution Center|0|0 *
* |Chicago|IL|60677-7009| *
* 06/09/2020|Associated Integrated Supply Chain Solutions|7954 *
* Solution Center|0|0 *
* |Chicago|IL|60677-7009| *
* 06/09/2020|Associated Integrated Supply Chain Solutions|7954 *
* Solution Center|0|0 *
* |Chicago|IL|60677-7009| *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Informix Server 12.10.xC15 (when available) or *
* 14.10.xC5. *
**************************************************************** | |
| Local Fix: | |
| Solution | |
| Workaround | |
****************************************************************
* USERS AFFECTED: *
* Users of Informix Server prior to 12.10.xC15 and 14.10.xC5. *
****************************************************************
* PROBLEM DESCRIPTION: *
* Using the following example: *
* *
* CREATE EXTERNAL TABLE tstbill *
* ( *
* as_of_date date, *
* remit_to_name char(50), *
* remit_to_addr2 char(50), *
* remit_to_addr3 char(50), *
* remit_to_addr4 char(50), *
* remit_to_city char(50), *
* remit_to_state char(3), *
* remit_to_zip char(12) *
* ) *
* *
* USING *
* (DATAFILES *
* ( *
* "DISK:/some/path/to/output/tstbill.txt" *
* ) *
* ); *
* *
* INSERT INTO tstbill *
* *
* select *
* today , *
* 'Associated Integrated Supply Chain Solutions', *
* '7954 Solution Center', *
* '0', *
* '0', *
* 'Chicago' , *
* 'IL' , *
* '60677-7009' *
* *
* From *
* ( *
* SELECT *
* today , *
* 'TEST' , *
* '' , *
* '' , *
* '' , *
* 'Chicago' , *
* 'IL' , *
* '60677-7009' *
* FROM *
* gtr gtr *
* WHERE *
* gtr.csc_id between '124360' AND '124360' AND *
* gtr.gla_id in ('14000') *
* order by 3,2 *
* ) *
* order by 1 *
* -- if you comment out the above order by 1, the data in the *
* external *
* -- table no longer looks corrupted *
* *
* If you have the "order by 1" in the select statement, when *
* you *
* look at the data generated in the tstbill.txt, you will see *
* output like the following: *
* *
* 06/09/2020|Associated Integrated Supply Chain Solutions7954 *
* S|olution Center00Ch *
* icagoIL60677-7009 *
* 06/09/2020|Associated Integrated Supply Chain Solutions7954 *
* S|olution Center00Ch *
* icagoIL60677-7009 *
* 06/09/2020|Associated Integrated Supply Chain Solutions7954 *
* S|olution Center00Ch *
* icagoIL60677-7009 *
* 06/09/2020|Associated Integrated Supply Chain Solutions7954 *
* S|olution Center00Ch *
* icagoIL60677-7009 *
* 06/09/2020|Associated Integrated Supply Chain Solutions7954 *
* S|olution Center00Ch *
* icagoIL60677-7009 *
* 06/09/2020|Associated Integrated Supply Chain Solutions7954 *
* S|olution Center00Ch *
* icagoIL60677-7009 *
* ... *
* *
* If the "order by 1" is removed from the select (so the order *
* by *
* on the derived table) the output looks correct. Like the *
* following: *
* *
* 06/09/2020|Associated Integrated Supply Chain Solutions|7954 *
* Solution Center|0|0 *
* |Chicago|IL|60677-7009| *
* 06/09/2020|Associated Integrated Supply Chain Solutions|7954 *
* Solution Center|0|0 *
* |Chicago|IL|60677-7009| *
* 06/09/2020|Associated Integrated Supply Chain Solutions|7954 *
* Solution Center|0|0 *
* |Chicago|IL|60677-7009| *
* 06/09/2020|Associated Integrated Supply Chain Solutions|7954 *
* Solution Center|0|0 *
* |Chicago|IL|60677-7009| *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Informix Server 12.10.xC15 (when available) or *
* 14.10.xC5. *
**************************************************************** | |
| Comment | |
Fixed in Informix Server 12.10.xC15 and 14.10.xC5. | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 09.06.2020 28.01.2021 28.01.2021 |
| Problem solved at the following versions (IBM BugInfos) | |
| Problem solved according to the fixlist(s) of the following version(s) | |
