At 10:50 AM 28/01/2004 +0100, Arno wrote:

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

Arno, this works - why can't it be used?
Here's a test case you can try on employee.fdb:

SELECT PO_NUMBER, SHIP_DATE,
(CASE
WHEN SHIP_DATE IS NULL THEN NULL
ELSE EXTRACT (MONTH FROM SHIP_DATE)
END) AS M,
(CASE
WHEN SHIP_DATE IS NULL THEN NULL
ELSE EXTRACT (YEAR FROM SHIP_DATE)
END) AS Y
FROM SALES
WHERE PO_NUMBER IN ('V9346200', 'V9345200');

cheers,
Helen