Getting rid of the "where" clause if a value = 'ADMIN'

Question

So there is the main table (named Table1) and one of the columns in that table has a column called company name. and there's a another table called Account and in that table it has the usernames of people and the company name that user is associated with. so only the information in table1 associated with the user's company should be shown, unless if they're an admin.

I'm working on an APEX app on Oracle

I currently have a query that looks like this:

Select
SUPPLIER,
sum(NUMBER_OF_TICKETS) TICKETS,
round((sum(NUMBER_OF_TICKETS*AMOUNT_PAYABLE__FOREIGN_CUR)*.1),2) COMMISION,
round(sum(NUMBER_OF_TICKETS*AMOUNT_PAYABLE__FOREIGN_CUR),2) TOTAL_SALES,
round((sum(NUMBER_OF_TICKETS*AMOUNT_PAYABLE__FOREIGN_CUR)-(sum(NUMBER_OF_TICKETS*AMOUNT_PAYABLE__FOREIGN_CUR)*.1)),2) COMPANY_OWE,
CURRENCY
FROM TABLE1
WHERE
 Supplier = (select COMPANYNAME from Account where lower(USERNAME)=lower(:APP_USER)) AND
     PURCHASE_DATE_AND_TIME >= TO_DATE(:P2_START)
      AND PURCHASE_DATE_AND_TIME < TO_DATE(:P2_END)+ 1
group by
SUPPLIER,
CURRENCY

I'm running into trouble with the "WHERE" function. Because I basically want to have a if...then... (or a case statement in this case) where

IF
((select COMPANYNAME from Account where lower(USERNAME)=lower(:APP_USER)) == 'COMPANY1' AND (select USERTYPE from Account where lower(USERNAME)=lower(:APP_USER)) == 'ADMIN')

THEN
show all the rows; aka, Supplier = (select COMPANYNAME from BBAccount where lower(USERNAME)=lower(:APP_USER)) in the query

Does anyone have any ideas on how to go about writing a query for this?
I tried doing

Case statement When statement Then NULL

It doesn't work

I tried drawing a diagram, let me know if this makes it more clear:
Diagram

Answer 1

As it is about Apex, I'd suggest you to create a function:

create or replace function f_is_admin_01 (par_app_user in varchar2)
  return number
is
  -- function returns 1 if PAR_APP_USER is admin; otherwise, it returns 0
  l_usertype  account.usertype%type;
begin
  select a.usertype
    into l_usertype
    from account a
    where lower(a.username) = lower(par_app_user);

  return case when l_usertype = 'ADMIN' then 1
              else 0
         end;

exception
  when no_data_found then
    return 0;
end;

Now, you can use it in query as

select ...
from account a join table1 t on a.companyname = t.companyname
where (lower(a.username) = lower(:APP_USER) or f_is_admin_01 (:APP_USER) = 1)
  and t.purchasedate ...

Such an approach (I mean, having a function) can be useful elsewhere; for example, if you want to show certain page region only to admins - you'd put

return f_is_admin_01(:APP_USER) = 1;

into region's "Server side condition" (its type would be "Function that returns Boolean").

See if it helps.

Answer 2

So there is the main table (named Table1) and one of the columns in that table has a column called company name. and there's a another table called Account and in that table it has the usernames of people and the company name that user is associated with. so only the information in table1 associated with the user's company should be shown, unless if they're an admin

I think that you could simply join - something like:

SELECT ...
FROM TABLE1 t
INNER JOIN ACCOUNTS a 
    ON  lower(a.username) = lower(:APP_USER)
    AND (a.companyname = t.companyname OR a.usertype = 'ADMIN')
WHERE
    t.purchase_date_and_time >= TO_DATE(:P2_START)
    AND t.purchase_date_and_time < TO_DATE(:P2_END) + 1
GROUP BY ...

The inner join on ACCOUNTS is there to implement the filtering logic: the user must either the user belongs to the same company, or have the admin type.

Answer 3

EXISTS(...) is your friend:

---

Select SUPPLIER
        ,sum(NUMBER_OF_TICKETS) TICKETS
        ,round((sum(NUMBER_OF_TICKETS*AMOUNT_PAYABLE__FOREIGN_CUR)*.1),2) COMMISION
        ,round(sum(NUMBER_OF_TICKETS*AMOUNT_PAYABLE__FOREIGN_CUR),2) TOTAL_SALES
        ,round((sum(NUMBER_OF_TICKETS*AMOUNT_PAYABLE__FOREIGN_CUR)
              -(sum(NUMBER_OF_TICKETS*AMOUNT_PAYABLE__FOREIGN_CUR)*.1)),2) COMPANY_OWE
        , CURRENCY
FROM TABLE1 t
WHERE EXISTS (
        SELECT 1
        from Account x
        where x.Supplier = t.COMPANYNAME
        AND lower(x.USERNAME) = lower(:APP_USER))
        AND x.PURCHASE_DATE_AND_TIME >= TO_DATE(:P2_START)
        AND x.PURCHASE_DATE_AND_TIME < TO_DATE(:P2_END)+ 1
        )
group by SUPPLIER, CURRENCY
        ;