Results 1 to 8 of 8
  1. #1
    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 '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?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Each CASE needs and END. You have an embedded CASE so it needs and END also.

    Andy

  3. #3
    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 '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>".

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by raysefo
    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

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

  6. #6
    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 '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?

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    never mind....
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Dec 2006
    Posts
    17
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •