I'm trying to write a control query to test for when the max of a date column in one table is less than the max of a date column in another table. The query is called from a batch file and the output is logged to a text file which is then sent by email if its size is different to 0.
The query I am using is as follows :
WHEN MaxK_Date < MaxT_DATE
THEN 'Dates should be added to K_DATE for the period ' || MaxK_DATE
|| ' to ' || MaxT_DATE || '.'
(SELECT max(k.k_date) MaxK_DATE, max(t.maturity) MaxT_DATE
FROM k_date k, t_trades t);
This query runs as I would expect. My problem is that I was expecting the result to be completely empty if my condition isn't met (ELSE null) but in fact this is returning a recordset with the column name K_DATES_CHECK and a null value in it. This is logged to my text output file and a mail is sent becaused the filesize property of the text file is different to 0.
Ideally if my case is not met I would like the script to exit completely and do nothing.
Does anyone know of a simple way of doing this ? I have played around with some possibilities but keep ending up back where I started.