SQL Query

QSense — Fri, 07 Jan 2011 14:09:19 GMT

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

topic SQL Query in QlikView

SQL Query