Why not posting the working SQL code, the non working 4GL code and show us the different outputs ?

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

SQL:
SELECT COUNT(*)
  FROM table1
 WHERE NOT EXISTS
 (SELECT item e
    FROM table2, table3
        WHERE prod_S_dept = filter_itema
        and prod_cat = filter_itemb
        and itemc = itemd)

4GL:
SELECT COUNT(*)
  FROM table1
 WHERE NOT EXISTS
 (SELECT item e
    FROM table2, table3
        WHERE prod_S_dept = filter_itema
        and prod_cat = filter_itemb
        and itemc = itemd)

The code is identical yet the results are different.  I understand that SQL is Diff't from 4GL but what I'm trying to understand is why the diff't results?  Pls understand this code sits in other  code which runs without a problem when the SQL is not in it

First, you don't tell us what the 4GL error is so I'm just guessing.

SQL statements are executed in a different context than in 4GL.  Executing in SQL is probably done in dbaccess/isql which returns the count.

In 4GL, typically the count in placed in a 4GL variable:



   

Be careful to preface all your fields with the table name.  If one of your variables happens to have the same name as a field name 4gl will use the variable over the field name.


If you want to debug your result using the code that olded gave you, you may want to add this after your query:

DISPLAY "Count is: ",cnt
SLEEP 5