Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    5

    Unanswered: Case Statement with IN Clause in DB2 for OS/390

    Hi,

    Following is the error what we are receiving when we are trying toexecute the below mentioned SQL statement using CASE STATEMENT with IN Clause in it.

    Can anybody throw some light on this? It will be highly appreciated.

    SQL0582N A CASE expression in a VALUES clause, IN predicate, GROUP BY clause,
    or ORDER BY clause cannot include a quantified predicate, an IN predicate using
    a fullselect, or an EXISTS predicate. SQLSTATE=42625


    select employee_no,employee_name,
    CASE
    When location in ('02','06') then 'one'
    When location in ('01','03') Then 'two'
    Else 'Three'
    End
    from employee where employee_no in ('123456','789654')

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Wrong sintax.

    Below are some hints from documentation. See the documentation for more detais.

    Hope this helps,
    Grofaty

    ---------------------------------------------------------------

    If the first character of a department number is a division in the organization, then a CASE expression can be used to list the full name of the division to which each employee belongs:
    SELECT EMPNO, LASTNAME,
    CASE SUBSTR(WORKDEPT,1,1)
    WHEN 'A' THEN 'Administration'
    WHEN 'B' THEN 'Human Resources'
    WHEN 'C' THEN 'Accounting'
    WHEN 'D' THEN 'Design'
    WHEN 'E' THEN 'Operations'
    END
    FROM EMPLOYEE;

    The number of years of education are used in the EMPLOYEE table to give the education level. A CASE expression can be used to group these and to show the level of education.

    SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME,
    CASE
    WHEN EDLEVEL < 15 THEN 'SECONDARY'
    WHEN EDLEVEL < 19 THEN 'COLLEGE'
    ELSE 'POST GRADUATE'
    END
    FROM EMPLOYEE

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    For your sample:
    select employee_no,employee_name,
    CASE
    When location = '02' then 'one'
    when location ='06' then 'one'
    when location = '01' then 'two'
    when location ='03' then 'two'
    Else 'Three'
    End
    from employee where employee_no in ('123456','789654')

    OR use this one (result is the same).

    select employee_no,employee_name,
    CASE location
    When '02' then 'one'
    when '06' then 'one'
    when '01' then 'two'
    when '03' then 'two'
    Else 'Three'
    End
    from employee where employee_no in ('123456','789654')

    Hope this helps,
    Grofaty

Posting Permissions

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