1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to execute a query based on a condition.,//!

Discussion in 'SQL PL/SQL' started by Vicky, Aug 23, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    How to execute a query based on a condition..


    select case when flag='Y' then (select * from employees order by salary)
    else (select * from employees order by emp_name)
    end
    from employees;

    the above query shows the error,
    ORA-00907: missing right parenthesis
    00907. 00000 - "missing right parenthesis"

    Could U correct the query.,!
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    The way you constructed the query is not logical.
    Secondly, It's not possible to do such an operation in a case statement.
    However, you can have a simple scalar query in the CASE statement, i.e, a query that returns zero or one row with exactly one column.

    For example:

    Code (SQL):
    SELECT CASE WHEN salary >1500 THEN (SELECT COUNT(*) FROM employees)
    ELSE (SELECT salary FROM employees WHERE rownum=1)
    END
    FROM employees;
    But of course, such a query doesn't make any sense :)

    Coming to your requirement (if I understand it correctly), I would rather suggest the following (sure there might be other ways):

    Code (SQL):
    SELECT * FROM employees
    ORDER BY decode(&flag,'Y',lpad(to_char(salary),10), last_name);

    OR

    Code (SQL):
    col X noprint
    SELECT
    CASE
      WHEN &flag = 'Y' THEN lpad(to_char(salary),10) ELSE last_name END X,
      e.*
    FROM employees e
    ORDER BY 1;
    P.S:
    - Input 'Y' or 'N' for &flag to check results.
    - I used "col X noprint" just to remove the sorting column from display
     
    Vicky likes this.
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi,

    Thanks, Rajen. Your query answers for my order by requirement.

    But wat I have to do, If the requirement is rom different tables,

    If the Flag is 'Y', I need to execute, the query below for Employees table:

    select * from employees order by salary;

    And If the Flag is 'N', I need to execute, the query below for Students table:

    select * from students order by Percentage;

    How can V achieve this.,/!

    Sorry, If the qns luks So Juvenile:)
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    Generally, you can't "set" the table name dynamically in SQL.
    However, PL/SQL provides several possibilities to easily achieve what you want for your requirement (dynamic sql, dbms_sql, etc...).

    A simple example would be:

    Input: 'Y' or 'N' for &flag

    Code (SQL):
    DECLARE
    rc             sys_refcursor;
    l_flag         varchar2(1) := &flag;
    l_rec_employee employees%rowtype;
    l_rec_student  students%rowtype;
    l_id           NUMBER;
    BEGIN
    OPEN rc FOR 'select * from '||
       CASE WHEN l_flag='Y'
          THEN 'employees order by salary'
          ELSE 'students order by percentage'
       END;
    loop
    CASE
      WHEN l_flag='Y' THEN
         fetch rc INTO l_rec_employee;
         l_id := l_rec_employee.employee_id;
      WHEN l_flag='N' THEN
         fetch rc INTO l_rec_student;
         l_id := l_rec_student.student_id;
      ELSE
         NULL;
    END CASE;
    exit WHEN rc%notfound;
    dbms_output.put_line('Id:'||l_id);
    END loop;
    END;
    /

    If you want something with only SQL, then with a little trick/workaround, you can have something quite close with:

    Input: Y or N for &flag (which is used as alias for the 2 in-line views).

    Code (SQL):
    SELECT DISTINCT &flag..* FROM (SELECT * FROM students) N, (SELECT * FROM employees) Y;
     
    Vicky likes this.
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thnk U so much, Rajen.:)

    Could U explain me the 2nd query;

    i.e,., why U're using distinct and 2 dots(..) after flag in the query,,/!
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    - 2 dots
    This is Oracle SQL syntax: if you need to have a "." immediately after a substitution variable, then you need to put 2 "."

    - Distinct
    There is no join between the two tables (students and employees) - and I don't see how they can be joined anyway :)
    So, the result is a cross join (cartesian product) of the 2 tables; if there are x records in students and y records in employees, you'll end up with (x * y) records, the same x records repeated y times. A "DISTINCT" will provide you with the unique records (assuming of course there are no duplicates within the tables).
     
    Vicky likes this.
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thannksss Again .,:)

    And 1 more qns.,. Is it possible to lock a package,.,

    If yes could you tell me how to lock and unlock it,.
     
  8. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Vicky likes this.