It’s surprising how many times you need to know whether a record exists in any one of two or more tables. Traditionally, you would have to check each table individually until you either find a match or have checked every table to confirm that no match exists.
Finding myself faced with just such a question, I started wondering whether I could check all of the tables in question in one go. And I can.
So I have a environment where records are written first to a transactions file and then, once the transactions are processed, to an archive. I need to know whether a transaction has been written to either table and if it hasn’t then I can do an insert.
My first check looked like this:
select (select count(*) from TRANSACTIONS where TRANSID = 677447208) as count1, (select count(*) from ARCHIVE where TRANSID = 677447208) as count2 from SYSIBM.SYSDUMMY1
This gives me a single row with a separate column for each count, which is a reasonable enough start and may prove to be useful at some point. What I really want right now, though, is a simple count of all records across all tables.
For this I have to adjust the query very slightly indeed:
select (select count(*) from TRANSACTIONS where TRANSID = 677447208) + (select count(*) from ARCHIVE where TRANSID = 677447208) from SYSIBM.SYSDUMMY1
… to get a single count of all occurrences of the transaction across as many tables as I need to check.