Branching between an Expression and IS NULL

Ikviens · #1 (**permalink**) 04-20-10, 10:23

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

dav1mo · #2 (**permalink**) 04-20-10, 11:14

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

Ikviens · #3 (**permalink**) 04-20-10, 11:27

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

it-iss.com · #4 (**permalink**) 04-21-10, 06:07

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;

Ikviens · #5 (**permalink**) 04-21-10, 09:11

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...... :-(