Tune this Query please

[dmohanty]

Hi All,

I have a query like this below to fetch data from DB2. However this query can't be used directly in QVD Generator to create the QVD, as it starts with 'WITH' keyword.

I need this query to be tuned so that it can hit the data base DB2 (with ODBC drivers) and I can store this in a QVD. Can anyone help me in tuning this query, or any other help (using subqueries) would help too. Please help in writing this query in a QVW file.

SUPP_REL_TABLE:

with supp_assoc_rel as (

SELECT distinct

god09.plant_cd as plant_cd

,god09.supp_cd as sa_supp_cd

,god09.supp_loc as sa_supp_loc

,god09.assoc_cd as sa_assoc_cd

,god10.assoc_name as sa_assoc_name

,god11.assoc_job_type as sa_job_type

from $(vSCHEMAPROD).god09d00 god09

inner join $(vSCHEMAPROD).god11d00 god11

on god11.plant_cd = god09.plant_cd

and god11.assoc_cd = god09.assoc_cd

and god11.assoc_job_type in ('01','13','04','10','08','14')

inner join $(vSCHEMAPROD).god10d00 god10

on god10.assoc_cd = god09.assoc_cd

),

cte_06 as (

select PLANT_CD as SA_PLANT_CD

,SA_SUPP_CD

,SA_SUPP_LOC

,SA_ASSOC_CD AS MS_ASSOC

,SA_ASSOC_NAME AS MATERIAL_SERVICE_NAME

FROM supp_assoc_rel

WHERE SA_JOB_TYPE = '01'

),

cte_13 as (

select PLANT_CD as SA_PLANT_CD

,SA_SUPP_CD

,SA_SUPP_LOC

,SA_ASSOC_CD AS PQ_ASSOC

,SA_ASSOC_NAME AS PARTS_QLTY_NAME

FROM supp_assoc_rel

WHERE SA_JOB_TYPE = '13'

),

cte_04 as (

select PLANT_CD as SA_PLANT_CD

,SA_SUPP_CD

,SA_SUPP_LOC

,SA_ASSOC_CD AS LOGISTICS_ASSOC

,SA_ASSOC_NAME AS LOGISTICS_NAME

FROM supp_assoc_rel

WHERE SA_JOB_TYPE = '04'

),

cte_10 as (

select PLANT_CD as SA_PLANT_CD

,SA_SUPP_CD

,SA_SUPP_LOC

,SA_ASSOC_CD AS NM_PLANNER_ASSOC

,SA_ASSOC_NAME AS NEW_MODEL_PLANNER_NAME

FROM supp_assoc_rel

WHERE SA_JOB_TYPE = '10'

),

cte_08 as (

select PLANT_CD as SA_PLANT_CD

,SA_SUPP_CD

,SA_SUPP_LOC

,SA_ASSOC_CD AS MP_GL_ASSOC

,SA_ASSOC_NAME AS MP_GL_NAME

FROM supp_assoc_rel

WHERE SA_JOB_TYPE = '08'

),

cte_14 as (

select PLANT_CD as SA_PLANT_CD

,SA_SUPP_CD

,SA_SUPP_LOC

,SA_ASSOC_CD AS NM_GL_ASSOC

,SA_ASSOC_NAME AS NM_GL_NAME

FROM supp_assoc_rel

WHERE SA_JOB_TYPE = '14'

)

select distinct

cte_06.SA_PLANT_CD

,cte_06.SA_SUPP_CD

,cte_06.SA_SUPP_LOC

,cte_06.MS_ASSOC

,cte_06.MATERIAL_SERVICE_NAME

,cte_13.PQ_ASSOC

,cte_13.PARTS_QLTY_NAME

,cte_04.LOGISTICS_ASSOC

,cte_04.LOGISTICS_NAME

,cte_10.NM_PLANNER_ASSOC

,cte_10.NEW_MODEL_PLANNER_NAME

,cte_08.MP_GL_ASSOC

,cte_08.MP_GL_NAME

,cte_14.NM_GL_ASSOC

,cte_14.NM_GL_NAME

from cte_06

left join cte_13

on cte_06.SA_SUPP_CD = cte_13.SA_SUPP_CD

and cte_06.SA_SUPP_LOC = cte_13.SA_SUPP_LOC

left join cte_04

on cte_06.SA_SUPP_CD = cte_04.SA_SUPP_CD

and cte_06.SA_SUPP_LOC = cte_04.SA_SUPP_LOC

left join cte_10

on cte_06.SA_SUPP_CD = cte_10.SA_SUPP_CD

and cte_06.SA_SUPP_LOC = cte_10.SA_SUPP_LOC

left join cte_08

on cte_06.SA_SUPP_CD = cte_08.SA_SUPP_CD

and cte_06.SA_SUPP_LOC = cte_08.SA_SUPP_LOC

left join cte_14

on cte_06.SA_SUPP_CD = cte_14.SA_SUPP_CD

and cte_06.SA_SUPP_LOC = cte_14.SA_SUPP_LOC  ;

[dmohanty]

Hi All,

Any tips/help on this please????

[#3 · Accepted solution]

Hello,

Try using below :

SUPP_REL_TABLE:

LOAD *;

SQL

with supp_assoc_rel as ( ..............

QV does not handle query execution, it leaves that the ODBC driver to that for us.

Once data is loaded to SUPP_REL_TABLE, it can then be used to store in a QVD.

Let me if it was helpful or anything else is required.

Thanks,

Angad

[dmohanty]

Hi Angad,

Thanks for the quick help. It seems I had missed the SQL statement in the query to write in QVW file.

Now I am able to create the QVD successfully and use it in the Data Model.

Thanks again.

Regards!!!