Informix: differences between two similar tables?

Question

I need to generate a report when I come across any differences in data in two almost identical tables. (I'm using Informix database Version 11.70.)

For instance: my live table has columns:

Live

Name    ID  TRN  
XXX      1   10

Archive:

Date        Name   ID   TRN  
01/01/2018  XXX    1    10  
31/12/2017  XXX    1    11  
29/12/2017  XXX    1    12

The Archive table has the same set of columns except that it also has a date column so I can see what values the live table had at a given date.

How would I write a query to find if there was change of values in live data when compared with the archived data.

I tried this query:

select name,id,TRN from live
union
select name,id,TRN from archive

Though it works, is there a better/faster way around as in report I need to have both live data and difference in historic values.

Expected results in report:

Live:
01/01/2018|XXX|1|10  
Archive
31/12/2017|XXX|1|11  
29/12/2017|XXX|1|12

Your sample query doesn't include the date fields; your expected output does. That's a conceptual disconnect. — Jonathan Leffler

Jonathan Leffler Jonathan Leffler · Accepted Answer · 2018-01-06T01:40:55

You could consider:

SELECT "Live" AS source, a.date, a.name, a.id, a.trn
  FROM archive AS a JOIN live AS l
    ON a.name = l.name AND a.id = l.id AND a.trn = l.trn
UNION  ALL
SELECT "Archive" AS source, a.date, a.name, a.id, a.trn
  FROM archive AS a
 WHERE NOT EXISTS(
       SELECT * FROM live AS l
        WHERE a.name = l.name AND a.id = l.id AND a.trn = l.trn
       )

This shows a row from Archive that matches each row in Live, and also shows each row in Archive without a direct match in Live. If you think you might have rows in Live that have no corresponding row in Archive, you'd need another term in your union, such as:

SELECT "Unarchived" AS source, NULL::DATE AS date, l.name, l.id, l.trn
  FROM live AS l
 WHERE NOT EXISTS(
       SELECT * FROM archive AS a
        WHERE l.name = a.name AND l.id = a.id AND l.trn = a.trn
       )

This selects those rows in Live that have no matching row in Archive. If you prefer, you can use TODAY or TODAY ± 1 (or some such expression) in place of NULL::DATE, depending on your requirements.

^{Warning: untested SQL — there could be bugs in it!}

Informix: differences between two similar tables?

2 Answers