Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    56

    Unanswered: Branching between an Expression and IS NULL

    Hello again,

    Code:
    DROP TABLE IF EXISTS temp_codes;
    CREATE TABLE temp_codes (end_year INT(4), code_value INT(1));
    INSERT INTO temp_codes VALUES (1990,1),(1991,2),(1992,3),(1994,4),(1998,5),(2005,6),(null,7);
    I have this table that keeps code values eligible until certain times. If the field end_year IS NULL, that means the record is alive or eligible for the current time.

    The SQL statement for retrieving the current code value is:
    Code:
    SELECT code_value 
      FROM temp_codes 
     WHERE end_year IS NULL;
    For historical values, take Year 1993 e.g., the relevant value is retrieved by looking for the least upper bound of 1993:
    Code:
    SELECT code_value 
      FROM temp_codes T1
     WHERE end_year =  (SELECT MIN(end_year)
                          FROM temp_codes T2
                         WHERE T2.end_year >= 1993);
    I am having trouble combining the two conditions in the WHERE clause to create a SELECT statement that works regardless of the current / historical distinction of the input year.

    Using the CASE construction looked promising but all formulations I tried just keep failing. Among them is the following that tries to find a value on the assumption that the input year is historical then, if this fails, look for the current one.
    Code:
    SELECT code_value 
      FROM temp_codes T1 
     WHERE end_year CASE WHEN EXISTS (SELECT * FROM temp_codes T2 WHERE end_year >= 1993)
                         THEN (SELECT MIN(end_year) FROM temp_codes T2 WHERE end_year >= 1993)
                ELSE NULL END;
    TIA
    Ik

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Code:
    SELECT code_value 
      FROM temp_codes T1
     WHERE end_year =  (SELECT MIN(end_year)
                          FROM temp_codes T2
                         WHERE T2.end_year >= 1993)
            OR end_year IS NULL;
    Dave

  3. #3
    Join Date
    Mar 2006
    Posts
    56
    Hi, Dave.

    Sorry but the script does not work because it retrieves not one but two rows (code_value = 4, 7; where only the former is relevant).

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    SELECT code_value
    FROM temp_codes T1
    WHERE end_year = (SELECT MIN(end_year)
    FROM temp_codes T2
    WHERE T2.end_year >= 1993)
    OR end_year IS NULL
    ORDER BY T1.end_year DESC
    LIMIT 1;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Mar 2006
    Posts
    56
    It's messy but it does the work. Thank you, Ronan.

    Actually my question is motivated by a comment in this Blog entry by Tom Kyte (he discussed a lot of Oracle-specific things so I am trying to bring issues about the Standard here).

    Quote Originally Posted by jimmyb
    I don't fear nulls. I fear developers that use default values in place of nulls. Such as, 31-DEC-9999 as a replacement for nulls.
    Okay, it looks like I should keep to the old 'expression BETWEEN effective_from AND effective_to' with a dummy date for the alive rows...... :-(

Posting Permissions

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