AnsweredAssumed Answered

Can I do a join with dual and avuser in a workflow?

Question asked by Steve Fuson on Jul 23, 2019
Latest reply on Aug 29, 2019 by Kristian Nordman

I'm trying to create a SQL Select query which gathers data from the database outside of what's available in dual in an approval workflow.  For instance, we have two applications where a user must already have an account with a specific role in application A before they can have an account in application B.  I want the approval workflow to check this, but I can't seem to get the query to work.  As best I can tell, if I select from avuser only, there's no context for the request.  But I can't get a join with workflow variables to work. 



SELECT d.${access_request_cri_meu_fullName} AS user, App.Role AS OtherRole FROM avuser.PV_UNIFIED_ENTITLEMENT App JOIN dual d ON app.USER_ID = d.${access_request_cri_meu_userId} WHERE app.APPLICATION_ID = 342


Is doing a join possible? If so, what would that look like?


Or could I do two SQL Select queries, one for dual then pipe the result into one for avuser?  If so, what would that look like?


Is the schema for dual written down somewhere?


Thank you.