Subquery variants returning impossible(?) results
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
)
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 |
+-----------------------------------------------------------+