0 Replies Latest reply: Jan 7, 2011 9:09 AM by ahnet ceylan RSS

    SQL Query

    ahnet ceylan

      Hello ,

      I want to execute below query in Qlickview.

      Is there anybody help me ?

      My regards,

       

      create table ZZTCP0VBAVKMD000 nologging as
      select a13.ID_GNR_YM ID_GNR_YM,
      a12.ID_GNR_CITY ID_GNR_CITY,
      sum(case when a11.ID_TKT_RTG_DOMINT_TYPE=1 then (a11.MT_SM_EXCESS_BGG_GROSS_EUR+a11.MT_SM_GROSS_EUR+a11.MT_SM_SPAM_EUR+a11.MT_SM_TOT_DU_EUR+a11.MT_SM_TOT_YR_EUR)/1.18 else (a11.MT_SM_EXCESS_BGG_GROSS_EUR+a11.MT_SM_GROSS_EUR+a11.MT_SM_SPAM_EUR+a11.MT_SM_TOT_DU_EUR+a11.MT_SM_TOT_YR_EUR) end) WJXBFS1
      from SM_SALES2 a11
      cross join LU_GNR_CITY a12
      cross join LU_DT_GNR_YM a13
      join LU_AGENT a14
      on (a11.ID_AGENT = a14.ID_AGENT)
      where (a14.ID_AGENT_MRKTG_CITY = a12.ID_GNR_CITY
      and a12.ID_GNR_AREA_GROUP not in (1)
      and a13.ID_GNR_YM between 200801 and 201010
      and a11.ID_TKT_REPORTING_YM = a13.ID_GNR_YM)
      group by a13.ID_GNR_YM,
      a12.ID_GNR_CITY

      Pass1 - Duration: 0:00:00.04
      create index ZZTCP0VBAVKMD000_i on ZZTCP0VBAVKMD000 (ID_GNR_YM, ID_GNR_CITY)

      Pass2 - Duration: 0:00:00.14
      ANALYZE TABLE ZZTCP0VBAVKMD000 ESTIMATE STATISTICS SAMPLE 10 PERCENT FOR TABLE FOR ALL COLUMNS SIZE 50

      Pass3 - Duration: 0:00:00.23
      create table ZZTT7IPC6U8MD001 nologging as
      select a13.ID_GNR_YM ID_GNR_YM,
      a12.ID_GNR_CITY ID_GNR_CITY,
      (CASE WHEN sum(a11.MT_MUH_BUT_POS_EUR)=0 THEN NULL ELSE sum(a11.MT_MUH_BUT_POS_EUR) END) WJXBFS1
      from BA_SALES_TYPE_YW a11
      cross join LU_GNR_CITY a12
      cross join LU_DT_GNR_YM a13
      where (a11.ID_AGENT_MRKTG_CITY = a12.ID_GNR_CITY
      and a12.ID_GNR_AREA_GROUP not in (1)
      and a13.ID_GNR_YM between 200801 and 201010
      and ((a13.ID_GNR_YM)
      in (select c21.ID_GNR_YM
      from LU_DT_GNR_YM c21
      cross join LU_DT_TKT_REPORTING_YM c22
      where c22.ID_TKT_REPORTING_YM = c21.ID_GNR_YM))
      and a11.ID_TKT_DATE_OF_TRNC_YM = a13.ID_GNR_YM)
      group by a13.ID_GNR_YM,
      a12.ID_GNR_CITY

      Pass4 - Duration: 0:00:00.04
      create index ZZTT7IPC6U8MD001_i on ZZTT7IPC6U8MD001 (ID_GNR_YM, ID_GNR_CITY)

      Pass5 - Duration: 0:00:00.11
      ANALYZE TABLE ZZTT7IPC6U8MD001 ESTIMATE STATISTICS SAMPLE 10 PERCENT FOR TABLE FOR ALL COLUMNS SIZE 50

      Pass6 - Duration: 0:00:00.23
      Rows selected: 5246
      select distinct a13.ID_GNR_COMP_REG ID_GNR_COMP_REG,
      a15.DS_GNR_COMP_REG DS_GNR_COMP_REG,
      coalesce(pa11.ID_GNR_CITY, pa12.ID_GNR_CITY) ID_GNR_CITY,
      a13.DS_GNR_CITY DS_DET_GNR_CITY,
      a13.DS_DET_GNR_CITY DS_DET_GNR_CITY0,
      a13.ID_GNR_CNTRY ID_GNR_CNTRY,
      a16.DS_GNR_CNTRY DS_GNR_CNTRY,
      a16.DS_DET_GNR_CNTRY DS_DET_GNR_CNTRY,
      coalesce(pa11.ID_GNR_YM, pa12.ID_GNR_YM) ID_GNR_YM,
      a14.DS_GNR_YM DS_GNR_YM,
      case when a14.ID_GNR_MONTH in (3,6,9,12) then to_char(to_date(a14.ID_GNR_YM,'YYYYMM') ,'Mon YY') else '.' end CustCol_153,
      pa11.WJXBFS1 WJXBFS1,
      pa12.WJXBFS1 WJXBFS2
      from ZZTCP0VBAVKMD000 pa11
      full outer join ZZTT7IPC6U8MD001 pa12
      on (pa11.ID_GNR_CITY = pa12.ID_GNR_CITY and
      pa11.ID_GNR_YM = pa12.ID_GNR_YM)
      join LU_GNR_CITY a13
      on (coalesce(pa11.ID_GNR_CITY, pa12.ID_GNR_CITY) = a13.ID_GNR_CITY)
      join LU_DT_GNR_YM a14
      on (coalesce(pa11.ID_GNR_YM, pa12.ID_GNR_YM) = a14.ID_GNR_YM)
      join LU_GNR_COMP_REG a15
      on (a13.ID_GNR_COMP_REG = a15.ID_GNR_COMP_REG)
      join LU_GNR_CNTRY a16
      on (a13.ID_GNR_CNTRY = a16.ID_GNR_CNTRY)

      Pass7 - Duration: 0:00:00.03
      [Populate Report Data]

      Pass8 - Duration: 0:00:00.07
      drop table ZZTCP0VBAVKMD000 purge

      Pass9 - Duration: 0:00:00.06
      drop table ZZTT7IPC6U8MD001 purge