How to select external data using join method.

Technote (FAQ)


Question

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.

Answer

External table doesn't support select where IN subquery condition

Example:
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.
Schema:
create table "informix".customer
(
customer_num serial not null ,
fname char(15),
lname char(15),
company char(20),
address1 char(20),
address2 char(20),
city char(15),
state char(2),
zipcode char(5),
phone char(18),
primary key (customer_num)
);

2) You have a text file "test.txt"
$ cat test.txt
101|
102|
105|

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
(customer_num integer)
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.

Syntax:
select * from customer c, ex
where
c.customer_num=ex.customer_num;

c) Result example.
$ dbaccess stores_demo -

Database selected.

> 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.

Rate this page:

(0 users)Average rating

Document information


More support for:

Informix Servers

Software version:

11.5, 11.7, 12.1

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Software edition:

Developer, Enterprise, Express, Growth, Innovator, Ultimate

Reference #:

1502066

Modified date:

2013-06-19

Translate my page

Machine Translation

Content navigation