You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

pEOPLE cODE Dynamic views calling Dynamic Views

6 replies [Last post]
Guest's picture
Guest

hi,
thank you for your example. Help me a lot, but my case is a little bit different.
I need to get the security row level base on the login user so I am using a dinamic view with the following code

SELECT rowsecclass
, oprid
FROM (
SELECT rowsecclass
, oprid
FROM SYSADM.psoprdefn )
WHERE %oprclause

With the RowsecClass I need to get the groups and display them in the LookPrompt so I am using a second dynamic view

SELECT group_id
FROM PS_TL_GRP_SECURITY
WHERE rowsecclass in (
SELECT rowsecclass
FROM p_row_sec_vw) <-- CALLING FIRST DINAMIC VIEW

when I run the page I have error "
SQL Error. #:650 Error position 95 Return: 942-ORA-00942 table or view does not exist"

I am not sure how I can accomplish this.. Any suggestion!! Please HELP ME!!

Thanks in advance

Lepa's picture
User offline. Last seen 1 year 43 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: pEOPLE cODE Dynamic views calling Dynamic Views

well for one, you last line FROM p_row_sec_vw is missing the ps_ it should be "FROM ps_row_sec_vw" - This will cause your table or view does not exist error. Fix that and try again :)

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Lizy's picture
Lizy (not verified)
Re: pEOPLE cODE Dynamic views calling Dynamic Views

Hi,
Thanks for your time. The last line p_row_sec_vw is a dinamyc view also so it is not part of the DB. However I tried with ps_p_row_sec_vw and still same error.

I tried this other SQL in a dynamic view (to avoid dinamyc view calling dynamic view), don't give error but it did not return anything?

SELECT group_id
FROM SYSADM.PS_TL_GRP_SECURITY
WHERE rowsecclass IN (
SELECT rowsecclass
FROM SYSADM.psoprdefn
WHERE %oprclause)

I appreciate any help. Thanks a lot!

Lepa's picture
User offline. Last seen 1 year 43 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: pEOPLE cODE Dynamic views calling Dynamic Views

I not sure what you are really tying to accomplish BUT why do you need to dynamic views? Why not put every thing into one like the following:

SELECT group_id
FROM PS_TL_GRP_SECURITY
WHERE rowsecclass in (SELECT rowsecclass
                           FROM (SELECT rowsecclass
                                   , oprid
                                   FROM ( SELECT rowsecclass
                                                 , oprid
                                                 FROM SYSADM.psoprdefn )
                                    WHERE %oprclause)
                       )

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Sri's picture
Sri
User offline. Last seen 11 years 34 weeks ago. Offline
Joined: 12/18/2008
Posts: 1
Re: pEOPLE cODE Dynamic views calling Dynamic Views

Try including either the OPRID or OPRCLASS in the search record for the page/ Component where the error occured.

Guest's picture
Guest (not verified)
Re: pEOPLE cODE Dynamic views calling Dynamic Views

Hey, ur error clearly showing that there is some table which left unbuilt...So check out for the that table and build it...

Guest's picture
Guest (not verified)
Re: pEOPLE cODE Dynamic views calling Dynamic Views

The error must be specifying which table/view does not exist. try to build that record again n the log file will show you where the error is during the build process. In case of view, this error occurs when there is some error in the sql n hence the view couldn't be built. Hope it helps.