Re: Subquery variants returning impossible(?) results

Jacob Salomon <jake@apparel.net> wrote: :Hi Family. : :Preliminary: INFORMIX-OnLine Version 7.13.UC5 :Machine: HP K-200 :OS: HP-UX B.10.01 : :I am getting some results that make no sense. Before I toss the query :at y'all, I will explain a litrtle of what I am doing. : :I have inherited a vast application, written almost entirely with :FourGen-generated code. One of many nice feature of FourGen is the help :text kept in table stxhelpd. When the help for a table and column, the :following applies: : :stxhelpd.hlp_module is the table name :stxhelpd.hlp_program is the column name : :My task: Find out how many tables/columns in our application no not have :help text listed in stxhelpd. : :How does my query distinguish an application table from a fourgen table? :Because my predecessors adopted the convention that all application :tables begin with the prefix "zz". (I originally chafed at that but the :benefits have outweighed my academic freedom. ;-) : :So here is my query: : : select unique tabname, colname : from systables t, syscolumns c : where t.tabid = c.tabid : and t.tabname matches "zz*" : and trim(tabname) || trim(colname) not in : (select trim(hlp_module) || trim(hlp_program) : from stxhelpd : ) This sub query may have a problem if hlp_module and/or hlp_program can ever be null. I don't know exactly what it will return in that case, but if it does return null it means it's unknown whether the tabname/columname is included in it or not. In this case "not in" will allways be false and ther will be no rows in your temp table. May be this is the problem. I haven't otherwise realy studied it to see anyting else. : into temp yutz1 : ; : select count(*) from yutz1; : : drop table yutz1; : :BTW, I used a temp table because of syntax restrictions: I really wanted :to code: : select count (unique(tabname, colname)) .... :but that gets me a syntax error. : :Anyway, the above was gioving me a zero count. Clearly impossible, :because there are for more columns lacking help text. And an almost :identical query [where.. IN] counted 507 entries. : :On a lark, I added a WHERE clause to the subquery, so the subquery is :now: : (select trim(hlp_module) || trim(hlp_program) : from stxhelpd : where hlp_module matches "zz*" : ) : :The active set of this new subquery is subset of the first version's :active set. So if the data I'm looking for is not in the original :active set, it should not be in this one either. But I tried it anyway. : :So much for impeccable logic! Now it tells me there that table yutz1 had :been filled with 9895 rows. This result makes far more sense than 0 but :why didn't this number show up with the first version of the subquery? : :BTW, I also tried running both versions of the subquery on their own. :With the where clause, it retrieved 2000+ rows; without it, it retrieved :over 12,000 rows. So it's not the subquery returning flawed data. : :Unless someone can point out a flaw in my reasoning and SQL, I cannot :trust the second count either, however sensible! : :HELP! :-- : -- Jake (In pursuit of undomesticated aquatic avians; : pursued by hypertensive, domesticated poultry) : :+-----------------------------------------------------------+ :| Impeccable Logic: A thought process which successfully | :| resists chicken bites | :+-----------------------------------------------------------+ 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