Dynamic sql creation

Rafal S

    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

      • 1. Re: Dynamic sql creation
        GregV

        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.

        • 2. Re: Dynamic sql creation
          Rafal S

          yes i meant union all

           

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

           

          Thanks !

          • 3. Re: Dynamic sql creation
            GregV

            Will there be always 2 columns in the select list? What about the join condition, is it the same for all tables involved?

            • 4. Re: Dynamic sql creation
              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

              • 5. Re: Dynamic sql creation
                Rafal S

                anyone ?

                • 6. Re: Dynamic sql creation
                  Rafal S

                  bump

                  • 7. Re: Dynamic sql creation
                    John Thorton

                    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?