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

    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?