Hi Set,

> > select
> > ( select case when (a.mdz is null) then 'A'
> > 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
> >)
> >
> >from
> >KEDjelatnik
> >
> >in results I get Null values where I think it should be 'A'. What am I doing wrong ?
>

SQL expression above is taken out of the context. I have to change value of field from table KEDjelatnik with calculated value.

select DjelatnikStatus
from KEDjelatnik
where <Search Condition 1>
and <Search Condition 2>

should became

select <SQL Expression> as DjelatnikStatus
from KEDjelatnik
where <Search Condition 1>
and <Search Condition 2>

> Rather, I'd recommend you to try something like:
>
> WITH TMP (djelatnikID, MDZ) as
> (select DjelatnikID, max(cast(stazzapdatumzaposljavanja as TimeStamp))
> from KEStazZap
> where cast(stazzapdatumprekida as timestamp) >= '2011-09-30'
> group by djelatnikID)
>

> SELECT case
> when a.mdz is null then 'A'
> when a.mdz > '2011-09-30' then 'B'
> when a.mdz < '2011-09-30' then 'C'
> else 'D'
> end
> FROM KEDjelatnik K
> LEFT JOIN TMP T ON K.DjelatnikID = T.DjelatnikID
>

I was considering this approach but I had following problem (not visible from my example SQL):

for one record in table KEDjelatnik I can have 1..n records in table KEStazZap


If I need DjelatnikStatus for one DjelatnikID I would have to calculate DjelatnikID, mdz pairs for all DjelatnikID in table KEStazZap.


As I already wrote to Arno, I didn't see that result of query which returns no records has null value only in context of KEDjelatnik.


Thank you for your answer.

Regards,
Zlatko