Results 1 to 8 of 8
Thread: Case Help Please?

012307, 13:43 #1Registered User
 Join Date
 Dec 2006
 Posts
 17
Unanswered: Case Help Please?
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 'MDL0.99'
when s.deep between 1.0 and 4.9 then '1.04.9'
when s.deep between 5.0 and 9.9 then '5.09.9'
when s.deep between 10.0 and 14.9 then '10.014.9'
when s.deep between 15.0 and 19.9 then '15.019.9'
when s.deep between 20.0 and 29.9 then '20.029.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?

012307, 14:23 #2Registered User
 Join Date
 Jan 2003
 Posts
 4,304
Provided Answers: 5Each CASE needs and END. You have an embedded CASE so it needs and END also.
Andy

012307, 14:36 #3Registered User
 Join Date
 Dec 2006
 Posts
 17
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 'MDL0.99'
when s.deep between 1.0 and 4.9 then '1.04.9'
when s.deep between 5.0 and 9.9 then '5.09.9'
when s.deep between 10.0 and 14.9 then '10.014.9'
when s.deep between 15.0 and 19.9 then '15.019.9'
when s.deep between 20.0 and 29.9 then '20.029.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>".

012307, 14:45 #4Registered User
 Join Date
 Jan 2003
 Posts
 4,304
Provided Answers: 5Originally Posted by raysefo
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

012307, 14:56 #5Registered User
 Join Date
 Dec 2006
 Posts
 17
i erased the s.deep beside the case and put ',' as you told me, it worked. Thanks

012307, 15:12 #6Registered User
 Join Date
 Dec 2006
 Posts
 17
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 'MDL0.99'
when s.deep between 1 and 4.9 then '1.04.9'
when s.deep between 5.0 and 9.9 then '5.09.9'
when s.deep between 10.0 and 14.9 then '10.014.9'
when s.deep between 15.0 and 19.9 then '15.019.9'
when s.deep between 20.0 and 29.9 then '20.029.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?

012307, 15:21 #7:)
 Join Date
 Jun 2003
 Location
 Toronto, Canada
 Posts
 5,516
Provided Answers: 1never mind....

"It does not work" is not a valid problem statement.

012307, 15:28 #8Registered User
 Join Date
 Dec 2006
 Posts
 17
CASE
when s.sonuc ='E' then 'E'
when s.sonuc ='A' then 'GELMEYEN'
when s.deep < 1 then 'MDL0.99'
when s.deep between 1 and 4.9 then '1.04.9'
when s.deep between 5.0 and 9.9 then '5.09.9'
when s.deep between 10.0 and 14.9 then '10.014.9'
when s.deep between 15.0 and 19.9 then '15.019.9'
when s.deep between 20.0 and 29.9 then '20.029.9'
when s.deep > 30.0 then '>30.0'
when s.deep > 0.1 then 'MDL'
END as SONUC