Scott Scott - 1 month ago 7
SQL Question

Complex LEFT JOIN not working as expected

DBMS is !

Here is my full query:

SELECT m.Name AS MessageType, COUNT(l.name) AS MessageCount, CAST(AVG(ResponseTime) AS DECIMAL(5, 2)) AS AvgResponseTime
FROM
(SELECT DISTINCT(name) FROM ENSLIB_HL7.Message) m LEFT JOIN
(
SELECT CAST(li.SessionId AS Bigint) AS session_id, li.name, MIN(li.TimeCreated) AS SessionStart, MAX(lo.TimeCreated) AS SessionEnd, CAST(DATEDIFF(s, MIN(li.TimeCreated), MAX(lo.TimeCreated)) AS DECIMAL(5, 2)) AS ResponseTime
FROM (SELECT h1.SessionId, h1.TimeCreated, $PIECE(RawContent, '|', 4), m1.name FROM ens.messageheader h1, ENSLIB_HL7.Message m1 WHERE h1.MessageBodyId = m1.id AND h1.TimeCreated > DATEADD(mi, -30, GETUTCDATE())) li
JOIN (SELECT h2.SessionId, h2.TimeCreated FROM ens.messageheader h2, ENSLIB_HL7.Message m2 WHERE h2.MessageBodyId = m2.id AND h2.TimeCreated > DATEADD(mi, -30, GETUTCDATE())) lo
ON li.SessionId = lo.SessionId
GROUP BY li.SessionId
) l on m.name = l.name
GROUP BY l.Name


This gives me 4 results:


VXU_V04 0 (null)
ADT_A03 3 0.01
ADT_A04 3 0.01
ADT_A08 143 0.01



Given that there is one result with 0 records, it seems like it is working. However, if I run
SELECT DISTINCT(name) FROM ENSLIB_HL7.Message
I get 10 results:


VXU_V04
ADT_A08
ACK_A08
ADT_A03
ACK_A03
ADT_A04
ACK_A04
ACK_V04
ADT_A01
ACK_A01



Why don't I get ten rows with my full query?

Answer

Change the GROUP BY to:

GROUP BY m.Name

You are aggregating by the column in the second table, so you only get one row for all the NULL values.

Most databases would reject this syntax, but apparently Intersystems Cache allows it.

Comments