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.

Categories: Notes

Leave a Reply

Your email address will not be published. Required fields are marked *