@sql in cache object script , sql explorer resulting different results
Hi Community,
I have below embeded sql query in object script class
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?
See Data Display Options
at first sight, it looks like an issue on variable scoping with embedded SQL
a quite old issue
try
%suspendedCount
instead ofsuspendedCount
If we open the source code of the class for the Status field, we will see the following:
Therefore , the following conclusions can be drawn:
Hence the leapfrog in the results.
Thanks Vitaliy Serdtsev
This answered my question
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