54719_en-US

liliconcom

Different result between using Query and open Database Query

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.

Re: Different result between using Query and open Database Query

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.

Re: Different result between using Query and open Database Query

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);

Re: Different result between using Query and open Database Query

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

Re: Different result between using Query and open Database Query

I am not an SQL expert, but from your query, it seems to imply BoardNo is a string.  Check that BoardNo is character in the data table.  The query could have stopped after 1 row.

 

That is all the recommendation have.