khioe@mona.Jpl.Nasa.Gov (Kent Hioe) (09/10/90)
Hi, I need some help/information from INFORMIX users. 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 ? 2. Is there any way to clear the field screen in INFORMIX-SQL run form menu (before or after query mode, after update, remove or add mode) if the form uses more than one table (i.e, involved in join table) ? Informix seems to clear the fields screen if and only if the table which the fields belongs to is active. (have to use master and detail mode). Thank you very much for your help and information. Kent Hioe khioe@mona.Jpl.Nasa.Gov elroy!mona.Jpl.Nasa.Gov!khioe
lugnut@sequent.UUCP (Don Bolton) (09/11/90)
In article <4779@jato.Jpl.Nasa.Gov> khioe@mona.Jpl.Nasa.Gov (Kent Hioe) writes: > >Hi, I need some help/information from INFORMIX users. > >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 ? > NONE I know of, I'd load it all into a "temp" table and then apply the logic to insert what you need to the real table. I have little respect for the Informix load utility. Usually use Oracle loader and then extract out in Informix acceptable format flat file and load from there. > >2. Is there any way to clear the field screen in INFORMIX-SQL run form menu > (before or after query mode, after update, remove or add mode) if > the form uses more than one table (i.e, involved in join table) ? > > Informix seems to clear the fields screen if and only if the table which > the fields belongs to is active. (have to use master and detail mode). > DUNNO here, been into 4gl stuff, and clears must be specified there ain't no automatics. > >Thank you very much for your help and information. > Hope it helps > >Kent Hioe >khioe@mona.Jpl.Nasa.Gov >elroy!mona.Jpl.Nasa.Gov!khioe
ggordon@agsm.ucla.edu (Glen Gordon, ,,) (09/12/90)
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
segel@balr.com (Michael Segel) (09/12/90)
In article <42135@sequent.UUCP> lugnut@sequent.UUCP (Don Bolton) writes: >NONE I know of, I'd load it all into a "temp" table and then apply the >logic to insert what you need to the real table. I have little respect >for the Informix load utility. Usually use Oracle loader and then extract >out in Informix acceptable format flat file and load from there. >> Well ISQL is fairly primitive. I think the best solution would be to load it into a temp file, then write a program in the querry language to make your final table. (Which is what is being recommended.) Now, IF you had 4gl, I would suggest using a C routine to load the data into a set of variables and pass this data on to a 4gl program to build your table of data. I agree that the Informix load utilities are primitive, I belive their primary function was to allow for a simple method of transfering data in and out of tables. However, I don't think that your solution "Usually use Oracle loader and then extract out in Informix acceptable ...." is too viable. Not many people have both Oracle and Informix and use both. I think it is , shall we say , cost prohibitive? Not that I am trying to bash Don for replying, but I think it interesting that his approach to solving the problem is to use Oracle to solve a deficiency in Informix. I mean, that if Don does a lot of 4gl work, why not make an Informix solution to solve the problem? Please take this comment with a grain of salt. I don't want anyone to flame me for something they thought I said. or that they have read into my posting. Cheers -Mike
lugnut@sequent.UUCP (Don Bolton) (09/14/90)
In article <1990Sep12.143033.6353@balr.com> segel@balr.UUCP (Michael Segel) writes: >In article <42135@sequent.UUCP> lugnut@sequent.UUCP (Don Bolton) writes: > >>NONE I know of, I'd load it all into a "temp" table and then apply the >>logic to insert what you need to the real table. I have little respect >>for the Informix load utility. Usually use Oracle loader and then extract >>out in Informix acceptable format flat file and load from there. >>> > Well ISQL is fairly primitive. I think the best solution would >be to load it into a temp file, then write a program in the querry language >to make your final table. (Which is what is being recommended.) Now, >IF you had 4gl, I would suggest using a C routine to load the data into >a set of variables and pass this data on to a 4gl program to build >your table of data. > I agree that the Informix load utilities are primitive, I belive >their primary function was to allow for a simple method of >transfering data in and out of tables. However, I don't think that >your solution "Usually use Oracle loader and then extract out in >Informix acceptable ...." is too viable. Not many people have both >Oracle and Informix and use both. I think it is , shall we say , >cost prohibitive? > >Not that I am trying to bash Don for replying, but I think it >interesting that his approach to solving the problem is to use >Oracle to solve a deficiency in Informix. I mean, that if Don does >a lot of 4gl work, why not make an Informix solution to solve >the problem? > Actualy I wasn't specific as to what my "usually" entails.. I recieve lists of names and addresses from our field offices for use in direct marketing campaigns, depending on the storage medium at their site and the unloaders literacy quotient with the product, the files I recieve vary from Informix unloads to label format files. The label format files are EASY to load into "O" the loader and its continueif etc make it a *simple* task. I should look into a 4gl solution, however at this point my time is fragmented to the point that I've just used the tools with which I am familiar. Now when it comes time to have the two database products cross communicate, I at least have that worked out. :-) >Please take this comment with a grain of salt. Yes I did... >I don't want anyone to flame me for something they thought I said. >or that they have read into my posting. > >Cheers >-Mike