Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL code -> 4GL code
- Thread starter Techone1
- Start date
- Thread starter
- #3
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
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:
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:
Code:
.
.
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
.
.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
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
- Status