Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2006

    Question Unanswered: SELECT CASE - Need Help on query

    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 || '.'

    ELSE null
    (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.

    Any ideas ?

    Peter HURRELL

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    SET HEADING OFF would produce output without column name.

    However, perhaps you shouldn't check whether file size is 0, but to send mail if, for example, file size > length('Dates should be added to K_DATE for the period 09.12.82 to 12.01.83.').

  3. #3
    Join Date
    Sep 2002
    Provided Answers: 1
    Try this:
    SELECT 'Dates should be added to K_DATE for the period ' 
           || max(k.k_date) || ' to ' || max(t.maturity)
    FROM   k_date k, t_trades t
    HAVING max(k.k_date) < max(t.maturity)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts