This content has been marked as final. Show 4 replies
As far as I know that is not possible. You enter a workspace and query from there, so the workspace is always your context. I don't think it is possible to create a query that would select records from different workspaces. But, I am not sure about that I have to admit.
You might be able to achieve this using DBMS_WM.Export, where you export several scenario's into a results table, then compare those results tables (see http://docs.oracle.com/cd/E11882_01/appdev.112/e11826/long_ref.htm#autoId23). That's the only way I see this happening, unless anybody else has any suggestions (Ben?)?
I initially didn't think this was going to be possible without creating staging tables (which would only require a CTAS sql statement, not a dbms_wm.export) as suggested, but the following might work:
1. Create a type that contains the required columns.
2. Create a pipelined function that returns a table type containing the row data and takes the workspace name as an argument. This function would execute GotoWorkspace, followed by the query, and then return the data. The database should be able to handle the differences in the session context since the queries are separate.
3. Create the sql query invoking the function one time for each workspace/context you are interested in. For example,
SQL> select * from table(return_ws_data('LIVE')) t1, table(return_ws_data('WS1')) t2 where ....
Note that after the query finishes, the session is in the workspace for whichever function executed last. So it would be a good idea to execute GotoWorkspace again after the query finishes to be sure as to which workspace the session is currently in.
Other possibilities would be to:
1. Use the MW view after executing SetMultiWorkspaces. However, this wouldn't work for any queries that require GotoDate. This view could be joined with a query from the original table name or with the another reference to the MW view using predicates to get the appropriate rows.
2. Using staging tables as mentioned
There have been attempts in the past to embed the GotoWorkspace into a function that is executed within a where clause predicate. I do not think that would work as outlined here: Re: Setting context inside a query - linear and reliable?
that is actually a brilliant idea! (wish I had thought of that ;-) ). If you create such a function, you could wrap it inside a procedure that can do the comparisons for you (say if you're interested in a total cost of different scenario's), if the business logic of the comparison is know at design time, thus automating this even further.
The reason I thought of the Export function, is that you can give it a workspace name so you do not need to go to the workspace first (which would be necessary with a CTAS I think), so you could do this with less statements, unless I'm mistaken. It's been a while since I've done any work with WM
I'd forgotten about the SetMultiWorkspaces. It is (in this case at least) too bad that "If the same row is selected from more than workspace, that row is shown only once." If there was a flag you could set that would turn this off, you'd basically have wat the OP wants I think. Maybe a change request for the future? I can see this being a huge advantage, being able to create multiple scneario's, then comparing them on a per-record basis.