Re: Getting data to a file, and divide by zero

In article: <nbornstein-0711952307060001@chinchilla.is.net> nbornstein@plr.com (Niel M. Bornstein) writes: > I am trying to get some raw data out of Informix. I have messed around > with the isql program, and I have a few questions. With Oracle, when I > run an SQL query in SQL*Plus, I get data in a columnar format. In isql, > however, I get it turned on its side, if you know what I mean. How can I > get a stream of data, without headers or page formatting of any kind, into > a file for importing into Oracle? > > A second question pertains to selecting expressions. Part of my query > involves dividing two columns, and the divisor may be zero. In Oracle, I > would use the decode() function to change any zeros in the divisor to ones > and I know I'd be safe from this problem. Is there an equivalent way to > do this in Informix? > > Thanks in advance for your help. > > First - column output: the easiest way of export data from an informix table is using the "unload" command. The select does give this output if and only if it will fit across the page. unload to "filename" select ....this will put all the data from the select into a "|" delimited file. If you need another delimiter character you can specify this : unload to "filename" delimiter "?" select .... where ? is the character to use.... As for the second problem.. Do you really want to divide by one when it should be zero ? There are at least two ways of doing this - you could write a short stored procedure that returns 1 if the parameter is 0 otherwise it returns the parameter Another approach is to use two select select col1/col2 from tab where col2<>0 union all select col1 from tab where col2=0 Hope that helps -- Mike Aubury