Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    15

    Unanswered: how to use table alias in case statement in where clause

    I have following query

    Code:
    Select emp_id, emp_name, department, salary
    from tbl_employee emp
    where (
    CASE 
          when emp.department = 'C'
          then (select count(*) from (SELECT 'Y' iscommissioner
                                      FROM tbl_emp_department ted
                                      where ted.department = emp.department )) 
    
          when emp.department = 'C'
          then (select count(*) from (SELECT 'Y' iscommissioner
                                      FROM tbl_emp_department ted
                                      where ted.department = emp.department
                                      and ted.id = 7))
    END)  > 0
    here I am getting the error emp.department invalid identifier

    What I am doing wrong? Why I can't access column alias in where clause in case statement?

    Any idea?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    ERROR?
    What Error?
    I don't see any error?
    Since we don't have your table, we can run posted SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    For starters your case statement makes no sense, you have duplicate test:
    Code:
    - - -  E t c  - - -
    CASE 
          when emp.department = 'C'
          then - - -
    
          when emp.department = 'C'
          then - - -
    
    END

    Now, if you are querying for both "emp.department = 'C' or != 'C'", then you can simplify as follows:
    Code:
    SELECT Emp_Id, Emp_Name, Department, Salary
      FROM Tbl_Employee Emp
     WHERE (Emp.Department = 'C'
        AND EXISTS
               (SELECT '?'
                  FROM Tbl_Emp_Department Ted
                 WHERE Ted.Department = Emp.Department))
        OR (Emp.Department != 'C'
        AND EXISTS
               (SELECT '?'
                  FROM Tbl_Emp_Department Ted
                 WHERE Ted.Department = Emp.Department
                   AND Ted.Id = 7));
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by LKBrwn_DBA View Post
    ...

    Now, if you are querying for both "emp.department = 'C' or != 'C'", then you can simplify as follows:
    Code:
    SELECT Emp_Id, Emp_Name, Department, Salary
      FROM Tbl_Employee Emp
     WHERE (Emp.Department = 'C'
        AND EXISTS
               (SELECT '?'
                  FROM Tbl_Emp_Department Ted
                 WHERE Ted.Department = Emp.Department))
        OR (Emp.Department != 'C'
        AND EXISTS
               (SELECT '?'
                  FROM Tbl_Emp_Department Ted
                 WHERE Ted.Department = Emp.Department
                   AND Ted.Id = 7));
    The query might be simplified some more, like the following examples.

    Example 1:
    Code:
    SELECT Emp_Id, Emp_Name, Department, Salary
      FROM Tbl_Employee Emp
     WHERE EXISTS
               (SELECT '?'
                  FROM Tbl_Emp_Department Ted
                 WHERE Ted.Department = Emp.Department
                   AND Emp.Department = 'C'
               )
        OR EXISTS
               (SELECT '?'
                  FROM Tbl_Emp_Department Ted
                 WHERE Ted.Department = Emp.Department
                   AND Emp.Department != 'C'
                   AND Ted.Id = 7
               )
    ;

    Example 2:
    Code:
    SELECT Emp_Id, Emp_Name, Department, Salary
      FROM Tbl_Employee Emp
     WHERE EXISTS
           (SELECT '?'
              FROM Tbl_Emp_Department Ted
             WHERE Ted.Department =  Emp.Department
               AND Emp.Department =  'C'
              OR   Ted.Department =  Emp.Department
               AND Emp.Department <> 'C'
               AND Ted.Id         =  7
           )

    Example 3:
    Code:
    SELECT Emp_Id, Emp_Name, Department, Salary
      FROM Tbl_Employee Emp
     WHERE EXISTS
           (SELECT '?'
              FROM Tbl_Emp_Department Ted
             WHERE Ted.Department = Emp.Department
              AND
              (    Emp.Department = 'C'
               OR  Ted.Id         = 7
              )
           )

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Shikhashah View Post
    I have following query

    Code:
    Select emp_id, emp_name, department, salary
    from tbl_employee emp
    where (
    CASE 
          when emp.department = 'C'
          then (select count(*) from (SELECT 'Y' iscommissioner
                                      FROM tbl_emp_department ted
                                      where ted.department = emp.department )) 
    
          when emp.department = 'C'
          then (select count(*) from (SELECT 'Y' iscommissioner
                                      FROM tbl_emp_department ted
                                      where ted.department = emp.department
                                      and ted.id = 7))
    END)  > 0
    here I am getting the error emp.department invalid identifier

    What I am doing wrong? Why I can't access column alias in where clause in case statement?

    Any idea?
    The query may get error on DB2!
    (Need LATERAL or TABLE keyword before innermost subqueries.)

    Are you sure of using Oracle?

Tags for this Thread

Posting Permissions

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