Starting with IBM® Informix® Server 11.50.xC6, you can create a table where the data is external to the database server. However, external table doesn't support select where IN subquery condition. Therefore, instead, this article will show you an example how to select external data using join method.
External table doesn't support select where IN subquery condition
Select test_name from test where test_no in < Txt file>.
instead, you can select external data using join method.
Here is the example:
1) Table "customer" is on database stores_demo.
create table "informix".customer
customer_num serial not null ,
primary key (customer_num)
2) You have a text file "test.txt"
$ cat test.txt
3) You would like to select data from table customer with the condition customer_num in "emp.txt"
What you can do is:
a) First create external table "ex" refer to "emp.txt".
$ cat ex.sql
create external table ex
using ( DATAFILES ("DISK:/IBM/informix/emp.txt"));
b) Since external table doesn't support select where IN subquery condition, you need to use join method.
select * from customer c, ex
c) Result example.
$ dbaccess stores_demo -
> select c.customer_num, c.fname, c.lname
> from customer c, ex
> where c.customer_num=ex.customer_num;
customer_num fname lname
101 Ludwig Pauli
102 Carole Sadler
105 Raymond Vector
3 row(s) retrieved.
NOTE: the txt file need to have dilimiter. e.g." | " like we have in our emp.txt.