Path: utzoo!utgpu!watserv1!watmath!att!tut.cis.ohio-state.edu!zaphod.mps.ohio-state.edu!usc!elroy.jpl.nasa.gov!ucla-cs!mara!agsm.ucla.edu
From: ggordon@agsm.ucla.edu (Glen Gordon,  ,,)
Newsgroups: comp.databases
Subject: Re: Need INFO from INFORMIX users.
Message-ID: <591@mara.cognet.ucla.edu>
Date: 12 Sep 90 05:44:41 GMT
References: <4779@jato.Jpl.Nasa.Gov>
Sender: news@mara.cognet.ucla.edu
Reply-To: ggordon@agsm.ucla.edu
Lines: 35

From article <4779@jato.Jpl.Nasa.Gov>, by khioe@mona.Jpl.Nasa.Gov (Kent Hioe):
> 
> 1. Is there any way in INFORMIX-SQL to load an ascii data file to a table with
>    some logical expression ? 
>    e.g: I have a table : customer consists of cust_no, invoice_bal, credit_bal.
> 		I would like to load data from an ascii file consists of
> 		cust_no and amount_figure.
> 		I would like to insert the data to invoice_bal if the amount_figure is
> 		positive amount, otherwise to credit_bal if negative amount ?
> 
Load the ASCII file to a temporary table and then execute the
Create a table, trans_table,  indexed on cust_no to temporarily store the 
contents of the ASCII file.  then
	begin work;
	load from "filename"
		insert into trans_table;
	insert into customer (cust_no,invoice_bal)
		select cust_no, amount_figure from trans_table	
		where amount_figure > 0;
	insert into customer (cust_no,credit_bal)
		select cust_no, amount_figure from trans_table	
		where amount_figure < 0;
	commit work;

For each record inserted into customer, either invoice_bal or
credit_bal will be NULL.

Hope this helps.

--Glen
--
Glen Gordon				| Internet: ggordon@agsm.ucla.edu
Computing Services			| Bitnet  : ggordon@uclagsm.bitnet
Anderson Graduate School of Management	| AT&Tnet : (213) 206-6590
University of California, Los Angeles	| Fax	  : (213) 206-2002