7 Replies Latest reply on Nov 25, 2019 5:16 PM by John Thorton

Dynamic sql creation

Rafal S Nov 18, 2019 9:51 AM

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

cC1,

cC2

from core left join Q_C

)

select

cA1,

cA2

cB1,

cB2

cC1

cC2

FROM RESULTS

1. Re: Dynamic sql creation

GregV Nov 18, 2019 10:39 AM (in response to Rafal S)

Yes you can build your statement using some pl/sql code. In your case UNION ALL would be better than UNION, unless the 2 columns you select can be both null. You should do all the unions and then left join the result to CORE so that you query the latter only once.
Like Show 0 Likes(0)

Actions
2. Re: Dynamic sql creation

Rafal S Nov 18, 2019 1:40 PM (in response to GregV)

yes i meant union all

Can You guide me how can i start with this in pl/sql ?

Thanks !
Like Show 0 Likes(0)

Actions
3. Re: Dynamic sql creation

GregV Nov 18, 2019 4:04 PM (in response to Rafal S)

Will there be always 2 columns in the select list? What about the join condition, is it the same for all tables involved?
Like Show 0 Likes(0)

Actions
4. Re: Dynamic sql creation

Rafal S Nov 18, 2019 9:34 PM (in response to Rafal S)

Hi
GregV !
So ...

Core data will be the same (i was thinking that i would always select all tables from "core" data)
The join condition will be different....

To simplify this what i would do is to have all of those data ready in initial Q_A,Q_B, Q_C (so already join inside of CTE)

How i see it is to lets say pass the CTE's which i would like to union and based on those the big union view will be created

Q_A 1 2 3 xxx xxx,core.*
Q_B x x x 123 xxx,core.*
Q_C xxxx xxx 123,core.*

I hope its all clear to You,
PS.Please find below more detailed version.
I think its now clear what i am trying to accomplish .
So basically i will be populating the data with more additional CTE's at the beginning ,and my goal with this automated script would be to pass all the CTE's i created and generate this big union all (Results).
You would be a star for me if You can help me with this !!!

SourceSystem1PV AS (

SELECT
   db1.v_SourceSystem1_pv.MetricSystem,
    db1.v_SourceSystem1_pv.MetricName,
    db1.v_SourceSystem1_pv."go1_MAP_TYPE",
    db1.v_SourceSystem1_pv."go1_MAP_ITEM_PHASE_CODE",
    db1.v_SourceSystem1_pv."go1_MAP_ITEM_PV_STATUS_CODE",
    db1.v_SourceSystem1_pv."go1_MAP_ITEM_ID",
    db1.v_SourceSystem1_pv."go1_SP_NAME",
    db1.v_SourceSystem1_pv."go1_MAP_ITEM_KEY",
    db1.v_SourceSystem1_pv."go1_PRODUCT_NAME",
    db1.v_SourceSystem1_pv."go1_MAP_ITEM_GEO_AREA_NAME",
    db1.v_SourceSystem1_pv."go1_MAP_NAME",
    db1.v_SourceSystem1_pv."go1_MAP_ITEM_ACTUAL_START_DATE",
    db1.v_SourceSystem1_pv."go1_MAP_ITEM_INITIAL_APPROVE_DATE",
    db1.v_SourceSystem1_pv."go1_MAP_ITEM_PLANNED_START_DATE",
    db1.v_SourceSystem1_pv."go1_MAP_ITEM_INITIAL_APPROVER",
    db1.v_SourceSystem1_pv."go1_MAP_CREATION_DATE",
    db1.v_SourceSystem1_pv."go1_MAP_THERAPEUTIC_AREAS",
    db1.v_SourceSystem1_pv."go1_MAP_RUN_BY_SP"
FROM
    v_SourceSystem1_pv
)
,
--SDQC
SourceSystem1SDQC as
(

SELECT
    db1.v_SourceSystem1_sdqc.metricsystem,
    db1.v_SourceSystem1_sdqc.metricname,
    db1.v_SourceSystem1_sdqc."go2_SDQC_ID",
    db1.v_SourceSystem1_sdqc."go2_MAP_ITEM_KEY",
    db1.v_SourceSystem1_sdqc."go2_MAP_NAME",
    db1.v_SourceSystem1_sdqc."go2_MAP_ITEM_GEO_AREA_NAME",
    db1.v_SourceSystem1_sdqc."go2_MAP_REGION",
    db1.v_SourceSystem1_sdqc."go2_MAP_GEO_SCOPE_NAME",
    db1.v_SourceSystem1_sdqc."go2_MAP_ENTITY",
    db1.v_SourceSystem1_sdqc."go2_MAP_ITEM_PHASE_CODE",
    db1.v_SourceSystem1_sdqc."go2_MAP_TYPE",
    db1.v_SourceSystem1_sdqc."go2_MAP_ITEM_PV_STATUS_CODE",
    db1.v_SourceSystem1_sdqc."go2_MAP_RUN_BY_SP",
    db1.v_SourceSystem1_sdqc."go2_S_CONDUCTED_BY",
    db1.v_SourceSystem1_sdqc."go2_SP_NAME",
    db1.v_SourceSystem1_sdqc."go2_S_SP_NAME",
    db1.v_SourceSystem1_sdqc."go2_MAP_ITEM_SOURCE_DATA_TYPES",
    db1.v_SourceSystem1_sdqc."go2_SDQC_ACTUAL_FREQUENCY",
    db1.v_SourceSystem1_sdqc."go2_S_NO_OF_REC",
    db1.v_SourceSystem1_sdqc."go2_S_SAMPLE_SIZE",
    db1.v_SourceSystem1_sdqc."go2_S_NO_OF_MISSED_ADVERSE_EVENTS",
    db1.v_SourceSystem1_sdqc."go2_S_COMPLETION_DATE",
    db1.v_SourceSystem1_sdqc."go2_S_DEADLINE_DATE",
    db1.v_SourceSystem1_sdqc."go2_S_DUE_DATE",
    db1.v_SourceSystem1_sdqc."go2_MAP_ITEM_ACTUAL_START_DATE",
    db1.v_SourceSystem1_sdqc."go2_MAP_ITEM_PLANNED_END_DATE",
    db1.v_SourceSystem1_sdqc."go2_MAP_ITEM_ACTUAL_END_DATE"
FROM
    db1.v_SourceSystem1_sdqc

)
,
SourceSystem1SPRT as (
SELECT
    db1.v_SourceSystem1_sprt.metricsystem,
    db1.v_SourceSystem1_sprt.metricname,
    db1.v_SourceSystem1_sprt."go3_MAP_ITEM_KEY",
    db1.v_SourceSystem1_sprt."go3_MAP_NAME",
    db1.v_SourceSystem1_sprt."go3_MAP_ITEM_GEO_AREA_NAME",
    db1.v_SourceSystem1_sprt."go3_COMPLETION_DATE",
    db1.v_SourceSystem1_sprt."go3_SP_NAME",
    db1.v_SourceSystem1_sprt."go3_DUE_DATE",
    db1.v_SourceSystem1_sprt."go3_MAP_ITEM_PV_STATUS_CODE",
    db1.v_SourceSystem1_sprt."go3_MAP_ITEM_PHASE_CODE",
    db1.v_SourceSystem1_sprt."go3_MAP_TYPE",
    db1.v_SourceSystem1_sprt."go3_MAP_RUN_BY_SP"
FROM
    db1.v_SourceSystem1_sprt
)

,

Results as (
select
/*SourceSystem1PV*/
SourceSystem1PV.*,
/*SourceSystem1SQC*/
null as go2_SDQC_ID ,
null as go2_MAP_ITEM_KEY ,
null as go2_MAP_NAME ,
null as go2_MAP_ITEM_GEO_AREA_NAME ,
null as go2_MAP_REGION ,
null as go2_MAP_GEO_SCOPE_NAME ,
null as go2_MAP_ENTITY ,
null as go2_MAP_ITEM_PHASE_CODE ,
null as go2_MAP_TYPE ,
null as go2_MAP_ITEM_PV_STATUS_CODE ,
null as go2_MAP_RUN_BY_SP ,
null as go2_S_CONDUCTED_BY ,
null as go2_SP_NAME ,
null as go2_S_SP_NAME ,
null as go2_MAP_ITEM_SOURCE_DATA_TYPES ,
null as go2_SDQC_ACTUAL_FREQUENCY ,
null as go2_S_NO_OF_REC ,
null as go2_S_SAMPLE_SIZE ,
null as go2_S_NO_OF_MISSED_ADVERSE_EVENTS ,
null as go2_S_COMPLETION_DATE ,
null as go2_S_DEADLINE_DATE ,
null as go2_S_DUE_DATE ,
null as go2_MAP_ITEM_ACTUAL_START_DATE ,
null as go2_MAP_ITEM_PLANNED_END_DATE ,
null as go2_MAP_ITEM_ACTUAL_END_DATE,
null as "go3_MAP_ITEM_KEY",
null as "go3_MAP_NAME",
null as "go3_MAP_ITEM_GEO_AREA_NAME",
null as "go3_COMPLETION_DATE",
null as "go3_SP_NAME",
null as "go3_DUE_DATE",
null as "go3_MAP_ITEM_PV_STATUS_CODE",
null as "go3_MAP_ITEM_PHASE_CODE",
null as "go3_MAP_TYPE",
null as "go3_MAP_RUN_BY_SP",
--/SourceSystem1--

core.*

from
SourceSystem1PV left join core on core.country=SourceSystem1PV."go1_MAP_ITEM_GEO_AREA_NAME"
and MetricSystem=SourceSystem

UNION ALL
Select
MetricSystem,
MetricName,
null as go1_MAP_TYPE,
null as go1_MAP_ITEM_PHASE_CODE,
null as go1_MAP_ITEM_PV_STATUS_CODE,
null as go1_MAP_ITEM_ID,
null as go1_SP_NAME,
null as go1_MAP_ITEM_KEY,
null as go1_PRODUCT_NAME,
null as go1_MAP_ITEM_GEO_AREA_NAME,
null as go1_MAP_NAME,
null as go1_MAP_ITEM_ACTUAL_START_DATE,
null as go1_MAP_ITEM_INITIAL_APPROVE_DATE,
null as go1_MAP_ITEM_PLANNED_START_DATE,
null as go1_MAP_ITEM_INITIAL_APPROVER,
null as go1_MAP_CREATION_DATE,
null as go1_MAP_THERAPEUTIC_AREAS,
null as go1_MAP_RUN_BY_SP,
"go2_SDQC_ID",
"go2_MAP_ITEM_KEY",
"go2_MAP_NAME",
"go2_MAP_ITEM_GEO_AREA_NAME",
"go2_MAP_REGION",
"go2_MAP_GEO_SCOPE_NAME",
"go2_MAP_ENTITY",
"go2_MAP_ITEM_PHASE_CODE",
"go2_MAP_TYPE",
"go2_MAP_ITEM_PV_STATUS_CODE",
"go2_MAP_RUN_BY_SP",
"go2_S_CONDUCTED_BY",
"go2_SP_NAME",
"go2_S_SP_NAME",
"go2_MAP_ITEM_SOURCE_DATA_TYPES",
"go2_SDQC_ACTUAL_FREQUENCY",
"go2_S_NO_OF_REC",
"go2_S_SAMPLE_SIZE",
"go2_S_NO_OF_MISSED_ADVERSE_EVENTS",
"go2_S_COMPLETION_DATE",
"go2_S_DEADLINE_DATE",
"go2_S_DUE_DATE",
"go2_MAP_ITEM_ACTUAL_START_DATE",
"go2_MAP_ITEM_PLANNED_END_DATE",
"go2_MAP_ITEM_ACTUAL_END_DATE",
null as "go3_MAP_ITEM_KEY",
null as "go3_MAP_NAME",
null as "go3_MAP_ITEM_GEO_AREA_NAME",
null as "go3_COMPLETION_DATE",
null as "go3_SP_NAME",
null as "go3_DUE_DATE",
null as "go3_MAP_ITEM_PV_STATUS_CODE",
null as "go3_MAP_ITEM_PHASE_CODE",
null as "go3_MAP_TYPE",
null as "go3_MAP_RUN_BY_SP",

core.*
from
SourceSystem1SDQC left join core on core.country=SourceSystem1SDQC."go2_MAP_ITEM_GEO_AREA_NAME"
and MetricSystem=SourceSystem

UNION ALL
Select
MetricSystem,
MetricName,
null as go1_MAP_TYPE,
null as go1_MAP_ITEM_PHASE_CODE,
null as go1_MAP_ITEM_PV_STATUS_CODE,
null as go1_MAP_ITEM_ID,
null as go1_SP_NAME,
null as go1_MAP_ITEM_KEY,
null as go1_PRODUCT_NAME,
null as go1_MAP_ITEM_GEO_AREA_NAME,
null as go1_MAP_NAME,
null as go1_MAP_ITEM_ACTUAL_START_DATE,
null as go1_MAP_ITEM_INITIAL_APPROVE_DATE,
null as go1_MAP_ITEM_PLANNED_START_DATE,
null as go1_MAP_ITEM_INITIAL_APPROVER,
null as go1_MAP_CREATION_DATE,
null as go1_MAP_THERAPEUTIC_AREAS,
null as go1_MAP_RUN_BY_SP,
/*SourceSystem1SQC*/
null as go2_SDQC_ID ,
null as go2_MAP_ITEM_KEY ,
null as go2_MAP_NAME ,
null as go2_MAP_ITEM_GEO_AREA_NAME ,
null as go2_MAP_REGION ,
null as go2_MAP_GEO_SCOPE_NAME ,
null as go2_MAP_ENTITY ,
null as go2_MAP_ITEM_PHASE_CODE ,
null as go2_MAP_TYPE ,
null as go2_MAP_ITEM_PV_STATUS_CODE ,
null as go2_MAP_RUN_BY_SP ,
null as go2_S_CONDUCTED_BY ,
null as go2_SP_NAME ,
null as go2_S_SP_NAME ,
null as go2_MAP_ITEM_SOURCE_DATA_TYPES ,
null as go2_SDQC_ACTUAL_FREQUENCY ,
null as go2_S_NO_OF_REC ,
null as go2_S_SAMPLE_SIZE ,
null as go2_S_NO_OF_MISSED_ADVERSE_EVENTS ,
null as go2_S_COMPLETION_DATE ,
null as go2_S_DEADLINE_DATE ,
null as go2_S_DUE_DATE ,
null as go2_MAP_ITEM_ACTUAL_START_DATE ,
null as go2_MAP_ITEM_PLANNED_END_DATE ,
null as go2_MAP_ITEM_ACTUAL_END_DATE,
"go3_MAP_ITEM_KEY",
"go3_MAP_NAME",
"go3_MAP_ITEM_GEO_AREA_NAME",
"go3_COMPLETION_DATE",
"go3_SP_NAME",
"go3_DUE_DATE",
"go3_MAP_ITEM_PV_STATUS_CODE",
"go3_MAP_ITEM_PHASE_CODE",
"go3_MAP_TYPE",
"go3_MAP_RUN_BY_SP",

core.*

from
SourceSystem1SPRT left join core on core.country=SourceSystem1SPRT."go3_MAP_ITEM_GEO_AREA_NAME"
and MetricSystem=SourceSystem

)
,

select * from results
Like Show 0 Likes(0)

Actions
5. Re: Dynamic sql creation

Rafal S Nov 22, 2019 8:26 AM (in response to Rafal S)

anyone ?
Like Show 0 Likes(0)

Actions
6. Re: Dynamic sql creation

Rafal S Nov 25, 2019 5:10 PM (in response to Rafal S)

bump
Like Show 0 Likes(0)

Actions
7. Re: Dynamic sql creation

John Thorton Nov 25, 2019 5:16 PM (in response to Rafal S)

Rafal S wrote:

bump
Please click on URL below & provide details as stated in #5 - #9 inclusive

How do I ask a question on the forums?
Like Show 0 Likes(0)

Actions

Go to original post