Building a union report option from Subject Area is missing OAC DV. Please guide how to create a union report in DV
You should be able to use a logical SQL query for your dataset. If you built a union query in classic Answers, its logical SQL can be copied from it and then pasted into a manual query in DV. Instead of pulling the subject area into your dataset, pull "Manual Query" into the dataset and paste the logical SQL into its Definition screen. Alternately, if you have already pulled the subject area into the dataset, go to Edit Definition and change the selected radio button from "Select Columns" to "Enter Logical SQL"; here you can paste (or type) the logical SQL for your union query.
Use Data Flow and create a dataset
We have data level security that is built in the RPD, and there are multiple dimensions/attributes used as prompt filters, We also have 6 yrs of data. Hence, Dataflow is not helpful. We need the data to be dynamic based on the filters selection
@Bala.01 , you can also check if it is possible to create a DB View in Autonomous Data Warehouse then import view into RPD and expose as fact table and let RPD handle joins/security. Hope it helps!
@Chere-Oracle I am able to create a data set with the union query. However, i am getting the below error while applying filters on the result columns. The same error occurs while applying on any columns
State: HY000. Code: 23007. [nQSError: 23007] Atomic table reference cannot be mixed with derived column references in the same SELECT. (HY000)
@RVohra - Here is our use case. We have customer level, department level, and detail level aggregates. Based on the filters selection, the corresponding aggregate will be picked dynamically with no change to the layout. We also use session variables in query to dynamically switch between measure columns based on the currency code selection in the filters. I am not sure if DB views will help in this case. Please confirm, i can try a POC.
@Bala.01 As I understand the error, it probably means you have included a column formula that uses the result of another column formula, when both columns are derived within the same SELECT statement. If you need the result one derivation (col1) to be used by another derivation (col2), then you have to put the col2 derivation in a separate SELECT statement, which selects FROM the first SELECT statement; e.g.,
SELECT (b.col1 + 10) AS col2 FROM (SELECT (a.net_price * 0.085) AS col1 FROM table a) b
