Same sql query return two different result.

Question

Question

CEDRIC CLERMONT · Apr 15, 2021

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 ?

Regards,

Cedric

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

Dmitry Maslennikov · Apr 15, 2021

That's interesting, but did you try to discover the differences, not only in the number of rows?

I would suggest, that the issue in time formats.

Try to play with different modes in SMP

0 0

score 0 · Answer 1 · 2021-04-15T11:20:02-04:00

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

score 0 · Answer 2 · 2021-04-15T11:16:09-04:00

Hello Cedric,

Aside from Dmitry's suggestion, you could add some debugging to check what namespace you are in right before your operation calls the query. What namespace do you have that would return 1875 rows for that query, if that namespace exists at all?

If you rule that out, you could reach out to the WRC to investigate.

score 1 · Answer 3 · 2021-04-16T04:19:32-04:00

CEDRIC CLERMONT · Apr 16, 2021

Thank u. Effectively the difference comes between the logical mode and the ODBC mode.

1 0