Home » Developer & Programmer » Application Express & MOD_PLSQL » Can I do this with SQL? (apex 4.0.2)
Can I do this with SQL? [message #539767] Wed, 18 January 2012 07:38 Go to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Hello Smile

I'm having a little trouble figuring out how to do this:
ok lets say I have a table ADMIN containing a list of administrators:admin1, admin2, admin3
and I have an ordinary SQL report that displays a bunch of requests made by different people:
select * from REQUESTS

what I want, is for each user to be able to see their requests only, unless they are an administrator.
so something like
SELECT * FROM
(SELECT * FROM REQUESTS)
WHERE CREATED_BY = :APP_USER

and then I'm stuck...is there a way to put:
IF :APP_USER IN (SELECT * FROM ADMIN) 'ignore that last bit' with SQL? or can I use PL/SQL in the report's source? In which case how would that be written?

thanks
Re: Can I do this with SQL? [message #539782 is a reply to message #539767] Wed, 18 January 2012 09:36 Go to previous messageGo to next message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
You could simply use an "and" and "or" in the query to add this restriction.
And (:APP_USER IN (SELECT username FROM ADMIN) 
     or CREATED_BY = :APP_USER)

You can also use PL/SQL to create the query dynamically. You then just return a varchar2 variable with your query.

But why are you using a subquery in the from clause here????

Re: Can I do this with SQL? [message #539895 is a reply to message #539782] Thu, 19 January 2012 02:24 Go to previous message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
oh of course I can...the stupid thing is I did something very similar on another app o_O...I was tired yesterday
thanks Smile
Previous Topic: Problem with a PL/SQL process...
Next Topic: Any value in doing a new install of Apex?
Goto Forum:
  


Current Time: Thu Jul 31 07:37:50 CDT 2014

Total time taken to generate the page: 0.11494 seconds