New post
Question
prashanth ponugoti · Apr 7, 2022

@sql in cache object script , sql explorer resulting different results

#Ensemble #InterSystems IRIS

Hi Community,

I have below embeded sql query in object script class

&sql(SELECT count(ID) into :suspendedCount FROM Ens.MessageHeader where TargetQueueName not like '_S%' and TargetQueueName not like 'ENS%' and Status='5')
resulting  1095 into suspendedCount

but same query executed in SQL explorer

SELECT count(ID)  FROM Ens.MessageHeader where TargetQueueName not like '_S%' and TargetQueueName not like 'ENS%' and Status='5'

resulting 0

but once i changed where condition  Status='5' to Status='Suspended'

SELECT count(ID)  FROM Ens.MessageHeader where TargetQueueName not like '_S%' and TargetQueueName not like 'ENS%' and Status='Suspended'

resulting  1095

same embeded sql query in object script class

&sql(SELECT count(ID) into :suspendedCount FROM Ens.MessageHeader where TargetQueueName not like '_S%' and TargetQueueName not like 'ENS%' and Status='Suspended' )
resulting  0 into suspendedCount

What is went wrong?

Product version: Caché 2017.1
2
0 151
Discussion (5)1
Log in or sign up to continue
Robert Cemper · Apr 7, 2022

at first sight, it looks like an issue on variable scoping with embedded SQL
a quite old issue
try %suspendedCount instead of suspendedCount
 

&sql(SELECT count(ID) into :%suspendedCount FROM Ens.MessageHeader where TargetQueueName not like '_S%' and TargetQueueName not like 'ENS%' and Status='Suspended' )
resulting  0 into suspendedCount
0 0
Vitaliy Serdtsev · Apr 7, 2022

If we open the source code of the class for the Status field, we will see the following:

Class Ens.DataType.MessageStatus Extends %Integer
{

Parameter DISPLAYLIST = ",Created,Queued,Delivered,Discarded,Suspended,Deferred,Aborted,Error,Completed";

Parameter VALUELIST = ",1,2,3,4,5,6,7,8,9";

}

Therefore , the following conclusions can be drawn:

  1. For Embedded SQL the RuntimeMode is Logical
  2. For SQL Explorer (Portal) the RuntimeMode is Display

Hence the leapfrog in the results.

0 0
prashanth ponugoti · Apr 7, 2022

thanks Robert ,

seems scope is not a problem , just I have changed in where condition Status='5'  

sql(SELECT count(ID) into :suspendedCount FROM Ens.MessageHeader where TargetQueueName not like '_S%' and TargetQueueName not like 'ENS%' and Status='5')

working fine

but same query  resulting 0 records in sql explorer, and revert the where condition working fine in sql explorer

why the difference where condition requiring for @sql ,sql explorer

thanks a lot,

Prashanth

0 0