Hi

I have a table and i want to use two different fields in a case statement like below: (s.result is varchar and s.deep is decimal fields)
...
case s.result
when 'E' then 'E'
when 'A' then 'GELMEYEN' ELSE
case s.deep
when s.deep < 1.0 then 'MDL-0.99'
when s.deep between 1.0 and 4.9 then '1.0-4.9'
when s.deep between 5.0 and 9.9 then '5.0-9.9'
when s.deep between 10.0 and 14.9 then '10.0-14.9'
when s.deep between 15.0 and 19.9 then '15.0-19.9'
when s.deep between 20.0 and 29.9 then '20.0-29.9'
when s.deep > 30.0 then '>30.0'
when s.deep > 0.1 then 'MDL'
END as RESULTS
...

But i got this error;
SQL0104N An unexpected token "<" was found following "e s.deep when s.deep". Expected tokens may include: "CONCAT "

Would you please help me to fix this case?

Each CASE needs and END. You have an embedded CASE so it needs and END also.

Andy

Here is the statement;

select t.SONUC,count(*) as TOPLAM FROM
(select
CASE s.sonuc
when 'E' then 'E'
when 'A' then 'GELMEYEN'
END
CASE s.deep
when s.deep < 1.0 then 'MDL-0.99'
when s.deep between 1.0 and 4.9 then '1.0-4.9'
when s.deep between 5.0 and 9.9 then '5.0-9.9'
when s.deep between 10.0 and 14.9 then '10.0-14.9'
when s.deep between 15.0 and 19.9 then '15.0-19.9'
when s.deep between 20.0 and 29.9 then '20.0-29.9'
when s.deep > 30.0 then '>30.0'
when s.deep > 0.1 then 'MDL'
END as SONUC
from
(dzmt.sonuc s
LEFT OUTER JOIN dzmt.dagitim d ON s.dagitimid = d.id),dzmt.priyotlar p
where
d.periyodid = p.id and
p.yil = 2007) as t
group by SONUC

and here is the error;
SQL0104N An unexpected token "CASE s.sonuc when 'E' then 'E' when '" was found following "TOPLAM FROM (select". Expected tokens may include: "<value_expr>".

You need a comma between the first case end and the next case. That is assuming that they are supposed to be different columns.

Andy

i erased the s.deep beside the case and put ',' as you told me, it worked. Thanks

sorry but it is NOT working! I checked twice.
select t.SONUC,count(*) as TOPLAM FROM
(select
CASE
when s.sonuc ='E' then 'E'
when s.sonuc ='A' then 'GELMEYEN'
END ,
CASE
when s.deep < 1 then 'MDL-0.99'
when s.deep between 1 and 4.9 then '1.0-4.9'
when s.deep between 5.0 and 9.9 then '5.0-9.9'
when s.deep between 10.0 and 14.9 then '10.0-14.9'
when s.deep between 15.0 and 19.9 then '15.0-19.9'
when s.deep between 20.0 and 29.9 then '20.0-29.9'
when s.deep > 30.0 then '>30.0'
when s.deep > 0.1 then 'MDL'
END as SONUC
from
(dzmt.sonuc s
LEFT OUTER JOIN dzmt.dagitim d ON s.dagitimid = d.id),dzmt.priyotlar p
where
d.periyodid = p.id and
p.yil = 2007) as t
group by SONUC

Because of group by SONUC , it only gets the second CASE statement part. How can i get all of the results from both case statements in one column?

never mind....

CASE
when s.sonuc ='E' then 'E'
when s.sonuc ='A' then 'GELMEYEN'
when s.deep < 1 then 'MDL-0.99'
when s.deep between 1 and 4.9 then '1.0-4.9'
when s.deep between 5.0 and 9.9 then '5.0-9.9'
when s.deep between 10.0 and 14.9 then '10.0-14.9'
when s.deep between 15.0 and 19.9 then '15.0-19.9'
when s.deep between 20.0 and 29.9 then '20.0-29.9'
when s.deep > 30.0 then '>30.0'
when s.deep > 0.1 then 'MDL'
END as SONUC