Different result between using Query and open Database Query

Question

Hi,

I switched from Open Database Query to Query(dt,"SQL") and foun that results are not similar. In Open Database Query I get 38 counts and in Quey I get 1 count. Is that possible?

Here is the attached script that compares the two. I appreciate your help. Thanks.

query_diff_JMP.jsl

AT · Answer

Thanks for your suggestions. It does work for both query type and results are similar now. But I am still curious why it did not work for query and did not result the same answer as open database query.

pmroz · Answer

Your JSL program has an extra double quote at the end of the first Open Database statement.  I don't think you need the subqueries, the group bys and the Order bys.  This SQL should be equivalent:

sql = 
"select distinct BoardNo 
   from Adata
  where BoardNo is not null
    and result <> 'PASS'";

result = Open Database("DSN=Name;UID=root;PWD=a**;", sql);

AT · Answer

Thanks. I did check the data table and BoardNo is a character and no error during the execution.