0
299

Written by

prashanth ponugoti
Technical Architect at OPTIMUS IT INFRA
Follow
Question prashanth ponugoti · Apr 7, 2022

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

#Caché

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
Discussion (5)0
Log in or sign up to continue

Comments

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
Vitaliy Serdtsev  Apr 7, 2022 to Robert Cemper

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

<FONT COLOR="#000080">Class Ens.DataType.MessageStatus Extends %Integer
</FONT><FONT COLOR="#000000">{

</FONT><FONT COLOR="#000080">Parameter </FONT><FONT COLOR="#000000">DISPLAYLIST = </FONT><FONT COLOR="#800080">",Created,Queued,Delivered,Discarded,Suspended,Deferred,Aborted,Error,Completed"</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Parameter </FONT><FONT COLOR="#000000">VALUELIST = </FONT><FONT COLOR="#800080">",1,2,3,4,5,6,7,8,9"</FONT><FONT COLOR="#000000">;
}</FONT>

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
prashanth ponugoti  Apr 7, 2022 to Robert Cemper

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