CODEWITHSUNDEEP
sqloracle-databaseif-statementplsqloracle-apex
Ibrahiem Rafiq
Ibrahiem Rafiq

Reputation: 73

PL/SQL Oracle Query With IF Statement

I want to implement a query that only returns the logged in user and displays there record only, which I have done as follows and it works:

SELECT * FROM EMPLOYEE
WHERE UPPER(username) = v('APP_USER')

However, I have another column called User_Type, and a user can be type 1, 2 or 3. If I have a user type of 1, I want the query to also return all the tables records too as user type 1 is an admin.

I thought about doing it like this:

BEGIN
SELECT * FROM Employee 
WHERE upper(username) = v('APP_USER')
IF User_Type = 1
THEN SELECT * FROM Employee
END IF;
END;
/

But it doesn't work in APEX Oracle PLSQL.

Any suggestions?

Upvotes: 1

Views: 14411

Answers (3)

Rachcha
Rachcha

Reputation: 8816

From what I understand you need to try this:

DECLARE
  emp employee%ROWTYPE; -- Create a record type
  tbl_emp IS TABLE OF emp;
  -- ^^^ Create a table of that record type
  v_user_type employee.user_type%TYPE;
  -- ^^^ Variable to store user type
BEGIN
  SELECT user_type
    INTO v_user_type
    FROM Employee 
   WHERE upper(username) = v('APP_USER');

  IF v_user_type = 1 THEN
    SELECT *
           BULK COLLECT INTO tbl_emp
      FROM employee;
    -- ^^ Returns the entire table
  ELSE
    SELECT *
           BULK COLLECT INTO tbl_emp
      FROM employee;
     WHERE upper(username) = v('APP_USER');
    -- ^^ Returns the row related to the user.
  END IF;
END;
/

The output is stored in the nested table variable tbl_emp.

EDIT:

It can be achieved using pure SQL also, like this:

SELECT *
  FROM employee e
 WHERE EXISTS (SELECT 1
                 FROM employees e_in
                WHERE e_in.user_type = 1
                  AND UPPER(e_in.username) = v('APP_USER'))
    OR UPPER(e.username) = v('APP_USER')

Choose whichever is best suited for you.

Upvotes: 7

user359040
user359040

Reputation:

Try:

select distinct e2.*
from employee e1
join employee e2 on (e1.username = e2.username or e1.User_Type = 1)
where UPPER(e1.username) = v('APP_USER')

Upvotes: 1

drquicksilver
drquicksilver

Reputation: 1635

You want all records from users with either UPPER(username) being v('APP_USER') or User_Type being 1? Then just use OR:

SELECT * FROM Employee WHERE upper(username) = v('APP_USER') OR User_Type = 1

If that's not what you mean, then can you explain more clearly?

Upvotes: 2

Related Questions