Re: SQL Question - The Answers --

rickt@wybbs.mi.org (rickt) wrote: :If I type select count( distinct col1) from foo :or I type select count( unique col1) from foo :I get the result of '4' :Though as you can see above the result is 5. :I also tried select count( distinct col1) from foo where col1 is null :and the result was 0 :**MORAL** :If you can guarantee that your column(s) do not have nulls, :then the distinct will work find, otherwise you may have to do the :more round about method with the temp table. Of course if you do: select count(*) from foo where col1 is nullyou will get 0 if there are no col1 with 0 or >0 if there are. Another alternative using a cursor: prepare c_null from select anycolumn from foo where col1 is nullopen c_null fetch c_null into l_anycolumn If a row is returned there is at least one where col1 is null. If this is the case or you got >0 in the above select you would have to add one to your count. Both methods can be used if you want to avoide the temporary table. The last will almost allways be faster. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org