SQL code -> 4GL code

[Techone1]

I created code in SQL that works. I then used the same code in 4GL but it doesn't work...no errors just the wrong answer. Does anyone know where I might look to find the problem?

 

[PHV]

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Techone1]

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

 

[olded]

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:

.
.
DEFINE cnt INTEGER

SELECT COUNT(*)INTO cnt
  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)

# and continue with your program
.
.

 

[dcumings]

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