New post
Question
· Nov 28

Embedded SQL - CODE 100

#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
	 )

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
Enrico Parisi · Nov 28

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

0 0