Embedded SQL - CODE 100
I have issue with one of my queries. That query work fine for ages, but suddenly stop works and giving me SQLCODE 100 in the routine.
&SQL(SELECT * FROM (
SELECTeventFROM dhr_log_lasers.production WHERE createDateUTC >= DATEADD(dd, -5, CURRENT_DATE) AND kiosk = :%var("kioskID") GROUP BY machine
HAVING ID = MAX(ID)
) WHERE event != 2
)If I copy this same query to the management portal and replace variable with real ID I will get back 6 or more rows.
Comments
My guess is that your %var local variable array is in conflict with the code executed by the embedded sql.
As you may know % local variables have global scope, therefore should be used with caution, particularly when calling "other code", like embedded sql in your case.
If a global scope % variable is needed, is advisable to follow the documentation on Local Variable Naming Convention:
Variable names starting with the % character are known as “percent variables” and have different scoping rules. In your code, for a local percent variable, start the name with %Z or %z; other names are reserved for system use.
I suggest to change your variable to %zvar or similar, if you do not need global scoping, remove the leading %.
Even I tried to replace %var with hardcoded value, in my case ID I got same result. SQLCODE 100 but from management portal SQLCODE 0 and returned 6 IDs.
I created new Index to the table and rebuild indices and now its works. But its weird why I got different result in embedded SQL and management portal.