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
