Embedded SQL - CODE 100

Question

Question

Patrik Spisak · Nov 28, 2024

#Management Portal #SQL #InterSystems IRIS

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 (
		SELECT event FROM 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
	 )ObjectScript
ObjectScript

If I copy this same query to the management portal and replace variable with real ID I will get back 6 or more rows.

Product version: IRIS 2024.1

$ZV: IRIS for Windows (x86-64) 2024.2 (Build 247U) Tue Jul 16 2024 09:52:30 EDT

Discussion (2)2

Log in or sign up to continue

score 0 · Answer 1 · 2024-11-28T08:11:43-05:00

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 %.

score 0 · Answer 2 · 2024-12-02T03:24:56-05:00

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.