Re: Subquery variants returning impossible(?) results
If the help is not specifically for a table,column (e.g. if its
only for a table) then hlp_program might be null. I'm not sure off
hand which way it screws up your query, but I'm pretty sure thats
the problem. The question is, "Is a non-null value in or not in
a set that has a null value in it?". I'm not inclined to think about
it at the moment, 'cause I'm on my way to Disneyland, but your
second 'Where' clause filters out null values, and so eliminates
confusing results.
Cheers,
Douglas Wilson
Jacob Salomon wrote:
> stxhelpd.hlp_module is the table name
> stxhelpd.hlp_program is the column name
> 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;
> 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*"
> )