Informix- HQL - self join

Question

I have a requirement to get the difference between on and off timestamps based on a condition. The timestamps are from the same column but the condition determines whether it is on or off. I need my result to display consecutive on and off timestamp in same row and their difference. Below query works in Maria DB but fails in Informix since subquery with limit are not permitted in select statement. Using: IBM Informix Developer Edition V11.70. I will be able to calculate the time difference but needing help with converting this query to be Informix and HQL compatible. Here is my code snippet:

SELECT
rec_id, 
last_change_ts as ontime, 
 (SELECT first last_change_ts 
         FROM trailer_log t_out 
    WHERE     t_out.rec_id = t_in.rec_id 
          AND t_out.last_change_ts >= t_in.last_change_ts 
          AND t_out.old_value = 'OH' 
  ORDER BY last_change_ts LIMIT 1 
          ) AS offtime
FROM
    trailer_log t_in
WHERE
    new_value='OH'
ORDER BY
    rec_id, last_change_ts

What would be the equivalent of doing this in Informix with HQL? Sample input data:

rec_id  last_change_ts new_value old_value
1188123 2017-10-09 12:37:46 OH  IN
1188123 2017-10-09 12:44:35 IN  OH
1188123 2017-10-09 12:57:12 OH  IN
1188123 2017-10-10 10:38:01 MT  OH
1188123 2017-10-10 10:44:30 OH  MT
1188133 2017-10-13 12:15:29 OH  HT
1188133 2017-10-13 12:24:07 HT  OH
1188143 2017-10-25 13:10:07 OH  MT
1188143 2017-10-25 13:15:56 MT  OH
1188148 2017-10-27 18:48:03 OH  HT
1188148 2017-10-31 17:27:09 SL  OH
1188150 2017-10-31 18:57:21 OH  HT
1188151 2017-11-09 07:57:51 OH  MT
1188151 2017-11-09 08:00:13 MT  OH
1188152 2017-11-10 14:56:48 OH  PL
1188152 2017-11-10 15:27:29 PL  OH

Expected output:

 rec_id    ontime                 offtime   
    1188123 10/9/2017 12:37     10/9/2017 12:44 
    1188123 10/9/2017 12:57     10/10/2017 10:38    
    1188123 10/10/2017 10:44        
    1188133 10/13/2017 12:15    10/13/2017 12:24    
    1188143 10/25/2017 13:10    10/25/2017 13:15    
    1188148 10/27/2017 18:48    10/31/2017 17:27    
    1188150 10/31/2017 18:57        
    1188151 11/9/2017 7:57      11/9/2017 8:00  
    1188152 11/10/2017 14:56    11/10/2017 15:27

Jonathan Leffler Jonathan Leffler · Accepted Answer · 2018-05-08T04:56:49

Solution for Informix

Tested with Informix 12.10.FC6 (I need to upgrade), but not using any features that are not also in 11.70, I came up with the following MCVE (Minimal, Complete, Verifiable Example).

The following SELECT statement:

SELECT t1.rec_id, 
       t1.last_change_ts AS ontime, 
       MIN(t2.last_change_ts) AS offtime
  FROM trailer_log AS t1
  LEFT OUTER JOIN trailer_log AS t2
    ON t2.rec_id = t1.rec_id
   AND t2.last_change_ts >= t1.last_change_ts
   AND t2.old_value = 'OH'
 WHERE t1.new_value = 'OH'
 GROUP BY t1.rec_id, t1.last_change_ts
 ORDER BY t1.rec_id, t1.last_change_ts;

produces the following output:

1188123     2017-10-09 12:37:46     2017-10-09 12:44:35
1188123     2017-10-09 12:57:12     2017-10-10 10:38:01
1188123     2017-10-10 10:44:30
1188133     2017-10-13 12:15:29     2017-10-13 12:24:07
1188143     2017-10-25 13:10:07     2017-10-25 13:15:56
1188148     2017-10-27 18:48:03     2017-10-31 17:27:09
1188150     2017-10-31 18:57:21
1188151     2017-11-09 07:57:51     2017-11-09 08:00:13
1188152     2017-11-10 14:56:48     2017-11-10 15:27:29

This agrees with the requested output, though it includes the seconds values in the time stamps and they're formatted in the ISO 8601 variant that is native to SQL.

The key insight is that for each record with a new value of 'OH', you want the next record with the same record ID and the earliest date (MIN) when the old value is 'OH' and the time stamp is not earlier than the first record, except when there is no later record, you still want to see the on-time. The 'except' translates into a left outer join. Note that it is crucial that the ON clause includes the filter condition on t2.old_value; if that is moved into the WHERE clause, you don't see the unmatched rows (those with a null off-time). If you want it in the WHERE clause, you'd have to use AND (t2.old_value = 'OH' OR t2.old_value IS NULL) instead.

On the sample data set, which has no indexes, the results were essentially instantaneous for me (0.001221 seconds). I'm not sure how well that'll scale to large data sets.

Working with HQL

There's a problem with HQL not managing to identify the type of the offtime in the original query. Various solutions using casts have been suggested and tried, but they've not yet helped.

Does this help at all?

SELECT t3.rec_id, t3.ontime, t3.offtime
  FROM (SELECT t1.rec_id, 
               t1.last_change_ts AS ontime, 
               MIN(t2.last_change_ts) AS offtime
          FROM trailer_log AS t1
          LEFT OUTER JOIN trailer_log AS t2
            ON t2.rec_id = t1.rec_id
           AND t2.last_change_ts >= t1.last_change_ts
           AND t2.old_value = 'OH'
         WHERE t1.new_value = 'OH'
         GROUP BY t1.rec_id, t1.last_change_ts
       ) AS t3
 ORDER BY t3.rec_id, t3.ontime;

Data

This is the DDL and DML I used to create the trailer_log table:

CREATE TABLE trailer_log
(
    rec_id          INTEGER NOT NULL,
    last_change_ts  DATETIME YEAR TO SECOND NOT NULL,
    new_value       CHAR(2) NOT NULL,
    old_value       CHAR(2) NOT NULL
);

INSERT INTO trailer_log VALUES(1188123, '2017-10-09 12:37:46', 'OH', 'IN');
INSERT INTO trailer_log VALUES(1188123, '2017-10-09 12:44:35', 'IN', 'OH');
INSERT INTO trailer_log VALUES(1188123, '2017-10-09 12:57:12', 'OH', 'IN');
INSERT INTO trailer_log VALUES(1188123, '2017-10-10 10:38:01', 'MT', 'OH');
INSERT INTO trailer_log VALUES(1188123, '2017-10-10 10:44:30', 'OH', 'MT');
INSERT INTO trailer_log VALUES(1188133, '2017-10-13 12:15:29', 'OH', 'HT');
INSERT INTO trailer_log VALUES(1188133, '2017-10-13 12:24:07', 'HT', 'OH');
INSERT INTO trailer_log VALUES(1188143, '2017-10-25 13:10:07', 'OH', 'MT');
INSERT INTO trailer_log VALUES(1188143, '2017-10-25 13:15:56', 'MT', 'OH');
INSERT INTO trailer_log VALUES(1188148, '2017-10-27 18:48:03', 'OH', 'HT');
INSERT INTO trailer_log VALUES(1188148, '2017-10-31 17:27:09', 'SL', 'OH');
INSERT INTO trailer_log VALUES(1188150, '2017-10-31 18:57:21', 'OH', 'HT');
INSERT INTO trailer_log VALUES(1188151, '2017-11-09 07:57:51', 'OH', 'MT');
INSERT INTO trailer_log VALUES(1188151, '2017-11-09 08:00:13', 'MT', 'OH');
INSERT INTO trailer_log VALUES(1188152, '2017-11-10 14:56:48', 'OH', 'PL');
INSERT INTO trailer_log VALUES(1188152, '2017-11-10 15:27:29', 'PL', 'OH');

Informix- HQL - self join

1 Answers

Solution for Informix

Working with HQL

Data