Hi,
SET already posted an answer, but here another one
SELECT
COALESCE(
(SELECT CASE
WHEN (CAST(a.mdz as timestamp) > CAST('2011-09-30' as timestamp)) THEN
'B'
WHEN (CAST(a.mdz as timestamp) < CAST('2011-09-30' as timestamp)) THEN
'C'
ELSE 'D' END
FROM (
select max(KEStazZap.stazzapdatumzaposljavanja) as mdz
from KEStazZap
where KEStazZap.DjelatnikID = KEDjelatnik.DjelatnikID
and cast( '2011-09-30' as timestamp) <=
cast(KEStazZap.stazzapdatumprekida as timestamp)
group by djelatnikID
) a
), 'A')
FROM
KEDjelatnik
btw, i doubt those casts are needed, certainly when those fields are of type
timestamp
You did "WHEN (a.mdz IS NULL)", but when there are no results for the
derived table "a" there is no result to test for null.
The a.mdz belongs to the context of the derived table, while the whole
select result of the sub-query belongs to the context of the table
KEDjelatnik. Thats's why this COALESCE should work as you wanted.
Regards,
Arno Brinkman
