Hi,

> What is the problem you want to solve? Do you want to find a way to
> prevent EXTRACT() being called if there is a null there? If so, try using
> CASE().
>
> select
> t1.id, t1.foo,
> t2.id, t2.test_month,
> t3.id, t3.test_date,
> (CASE
> t3.test_date WHEN NULL THEN NULL
> ELSE Extract(Month from t3.test_date)
> END) AS M,
> (CASE
> t3.test_date WHEN NULL THEN NULL
> ELSE Extract(Year from t3.test_date)
> END) AS Y

You can't use NULL this way in CASE the only way to test for NULL is :

SELECT
(CASE
WHEN t3.test_date IS NULL THEN NULL
ELSE Extract(Month from t3.test_date)
END) AS M,
(CASE
WHEN t3.test_date IS NULL THEN NULL
ELSE Extract(Year from t3.test_date)
END) AS Y

to make it a little shorter :

SELECT
(CASE
WHEN t3.test_date IS NOT NULL
THEN EXTRACT(Month from t3.test_date)
END) AS M,
(CASE
WHEN t3.test_date IS NOT NULL
THEN EXTRACT(Year from t3.test_date)
END) AS Y

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81