Re: QUERY on data for null values

In <93111.151516U08593@uicvm.uic.edu> Rakesh Goyal <U08593@uicvm.uic.edu> writes: >Hi netters: >I have some questions for informix experts regarding SQL. Let me briefly >explain what I am trying to accomplish. >I am using INFORMIX SE-5.0 and INFORMIX I-SQL 4.10 version >I created a database with 1 table. The table has 190 fields. All the >data is numerical (eg 0,1,2,9 etc). Each field is at most 4 columns long and >I have 220 columns. I'm not sure what you mean here. For the purposes of my answer I am assuming that you mean 190 columns with at most 4 digits totalling 220 digits in all. >after creating the database, I loaded up the ascii datafile using the DBLOAD >which worked file. All the fields have the right data inserted into them. >My datafile is 200 rows long. >I am interested in obtaining all the null value fields only for each row. >I tried the following: >select * from table (my fields are q1, q2, q3 etc) >where q1 is null >or q2 is null >or q3 is null >the problem is I am getting not only the null values but all the other fields >as well that has data. >(I am only interested in displaying the all the field names that have null > value for each row, I am trying to accomplish all missing values fields) >I am not sure if i have explained the problem properly. But anyway, any >suggestions are welcome. I don't think you can do what you are after in one pass. I think you may be looking for something like the following shell script: i=1 while [ $i -le 190 ] do isql db_name - > q$i.bad <<! select rowid, q$i from table where q$i is null;! i=`expr $1 + 1` done (NB I haven't checked the syntactic accuracy of the above by actually trying to run it, treat it as pseudo-code) You really should use your Primary Key column(s) rather than rowid, but you don't tell us what they are. I get the impression you might not have a primary key (which is a BAD habit to get into). graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.