· Apr 15, 2021

Same sql query return two different result.

Hello everyone,

First, thanks in advance for your help :-)

I noticed that a query called directly in a method of a class of a business operation does not return the same result as if I apply the same query in the same Caché database from Squirrel !!! 

I don't understand why ??? 

Here is the query : 

SELECT Header.MessageBodyId, MsgBody.Name AS typMessage
FROM Ens.MessageHeader Header
INNER JOIN EnsLib_HL7.Message MsgBody on Header.MessageBodyId = MsgBody.ID AND MsgBody.TimeCreated >= '2021-04-12 00:00:00' AND MsgBody.TimeCreated <= '2021-04-12 02:59:59' AND MsgBody.DocType LIKE '%ADT%' AND MsgBody.Name LIKE 'ADT_%'
WHERE ((Header.SourceConfigName = 'Hl7AdtTcpEprOut' OR Header.TargetConfigName = 'Hl7AdtTcpEprOut' )
AND Header.TimeCreated >= '2021-04-12 00:00:00'
AND Header.TimeCreated <= '2021-04-12 02:59:59'
AND Header.IsError = 0)

If I do it on Squirrel, i find 213 rows. 

If I search in the intersystems portal, I find 213 result

If I do exactely the same query in my method in business operation, i find 1875 rows ??? 

Can't you explain or help me to understand why ?



Product version: HealthShare 2018.1
$ZV: Cache for Windows (x86-64) 2018.1.4 (Build 505_1U) Thu May 28 2020 10:01:40 EDT [HealthShare Modules:Core:15.032.9035 + Linkage Engine:15.032.9035]
Discussion (4)0
Log in or sign up to continue

Piggybacking on Dmitry's comment, here's the documentation for:

Data Display Options

"For most data types, the three SelectMode modes return the same results. The following data types are affected by the SelectMode option:

Date, Time, and Timestamp data types. InterSystems SQL supports numerous Date, Time, and Timestamp data types (%Library.Date, %Library.Time, %Library.PosixTime, %Library.TimeStamp, and %MV.Date). With the exception of %Library.TimeStamp, these data types use different representations for Logical, Display, and ODBC modes."