I would like to run a select statement for a what if analysis where I join two tables by a column. How do I run a sql statement where one table I would like query from the LIVE version vs the other table where I'd like to query from a different workspace?
Or in other words, I want to run this SQL query where I want to use different gotodate for the two tables? It appears to me this is a standard use case of a what if analysis, and could be achieved in workspaces. But then for every scenario, I need to create a new workspace. Is there an easier way to achieve this if I have to run many scenarios at the same time?
TableA: id 456 version 5 ---- table B: id: 456 version 40
TableA: id 456 version 5 ---- table B: id: 456 version 45
TableA: id 456 version 5 ---- table B: id: 456 version 55
TableA: id 456 version 5 ---- table B: id: 456 version 66
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
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.