Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dynamic sql creation

Rafal SNov 18 2019 — edited Dec 3 2019

Hi !

I was wondering if You could help me with this ....

I have to build a view for Tableau reporting.

I have only one dimension which is common for all source systems (core) which i would like to join with all of the Source systems.

After that i would like to union all of the source systems with country dimension.

If i do it manually its pretty simple-just need to add some codes in couple of places.

I was thinking if there is a way to generate all this union query.

So i would give lets say 3 (and later more) tables and as result would have automaticly generated union view.

Maybe some dynamic sql would do the trick ?

Any help appreciated.

WITH CORE AS /*one shared dimension*/

(

select country from MDM

)

,Q_A AS  /*Source system A*/
(

  select

    cA1,

    cA2

  from A

)

  ,Q_B AS /*Source system b*/

(

  select

    cB1,

    cB2

  from B

)

   ,Q_C AS /*Source system C*/

(

  select

    cC1,

    cC2

  from B

)

,RESULTS AS

(

   select

country

cA1,

cA2

null

null

null

null

  from core left join Q_A

  union

select

   country

   null

   null

  cB1,

    cB2

null

null

  from core left join Q_B

select

country
null
null

null

null

cC1,

    cC2

  from core left join Q_C

)

select

cA1,

cA2

cB1,

   cB2

cC1

cC2

FROM RESULTS

Comments

Celvin Kattookaran

Read only access on form - this is not "read" only, this just means that the user cannot edit the form (that's there is a Write access on form, if you give Write access, an interactive user can edit the form)

To make a form read only, there is an option in display where you can make the form read only for all users, this will be applied to all users.

Regards

CK

USER1211

There is a [Read only] option in the data form itself. By checking that it makes the data form red only to everyone.

VM

On access, as specified by CK  and User1211, read access (on form) will not make form read only however if you want to make it read-only only for some users then you may consider following options:

1. Identify a member in a form (especially in POV) which you can assign those users a read access. But with this, those users will not be able to write / input data for that member (in any other forms).

2. Create a duplicate form and make it read-only.

    With this there could be some other stuff that you may have to consider as how will you handle if this form is in a Task List and so on.

Hope this helps.

VM

1 - 3

Post Details

Added on Nov 18 2019
8 comments
260 views