cross join problem 1

[suchitha]

hi

i have a probelm with two conditonal metrics(both have a time filter at report level). they work fine when i run them both alone(the data comes back good) but when i run them along with any of the other metrics,they screw up the other metrics also and duplicates the metric columns(some of the columns under the metric come back empty).there is a cross join in the select statement between two tables. would appreciate any help at all

sid

 

[entaroadun]

Please post the SQL and metric structures.

 

[suchitha]

I am posting the sql generated from three simple reports. The first report would contain just the first metric and an attribute from two unrelated but frequently used dimensions (like customer type and Store State). The second report would contain the same two attributes but
with the second metric instead of the first. The last report will contain both metrics with any addition metric needed to generate the problem sql.

1st report :
Tables Accessed:
LU_TM_DAY
MOB_SAMEDAY_DATA
MOB_LOC_DEF
LU_MERCH_LBR_DEPT
LU_GEOG_LOC

SQL Statements:
Pass0 - Duration: 0:00:04.53
select a13.LBR_DEPT_ID LBR_DEPT_ID,
a13.LBR_DEPT_NM LBR_DEPT_NM,
a11.LOC_ID LOC_ID,
a15.LOC_NM LOC_NM,
a12.CALNDR_DT CALNDR_DT,
count(a11.SCENARIO_ID) Same_Day_4_Avg
from U29PRPA.MOB_SAMEDAY_DATA a11,
LU_TM_DAY a12,
LU_MERCH_LBR_DEPT a13,
U29PRPA.MOB_LOC_DEF a14,
PROD.LU_GEOG_LOC a15
where a11.LOC_ID = a14.LOC_ID and
a11.LOC_ID = a15.LOC_ID
and (a14.RCD_START_DT <= (sysdate)
and NVL(a14.RCD_END_DT , SYSDATE+1) >= SYSDATE
and a11.SCENARIO_ID between (Select MAX(SCENARIO_ID) -3 from U29PRPA.MOB_SAMEDAY_DATA) and (Select MAX(SCENARIO_ID) from U29PRPA.MOB_SAMEDAY_DATA)
and a14.MOB_STATUS in ('STANDARD')
and a12.CALNDR_DT = (select MAX(CALNDR_DT) from PROD.LU_TM_DAY where FISC_MTH_ID = (select FISC_MTH_ID from PROD.LU_TM_DAY where calndr_dt = trunc(sysdate-30))))
group by a13.LBR_DEPT_ID,
a13.LBR_DEPT_NM,
a11.LOC_ID,
a15.LOC_NM,
a12.CALNDR_DT

2nd report:
Tables Accessed:
BPS_FIN_LOC_SKU_DT
MOB_LOC_DEF
LU_MERCH_LBR_DEPT
LU_GEOG_LOC


SQL Statements:

Pass0 - Duration: 0:02:07.61
select a11.LBR_DEPT_ID LBR_DEPT_ID,
a13.LBR_DEPT_NM LBR_DEPT_NM,
a11.LOC_ID LOC_ID,
a14.LOC_NM LOC_NM,
a11.CALNDR_DT CALNDR_DT,
sum(a11.UNIT) WJXBFS1
from BPS_FIN_LOC_SKU_DT a11,
U29PRPA.MOB_LOC_DEF a12,
LU_MERCH_LBR_DEPT a13,
PROD.LU_GEOG_LOC a14
where a11.LOC_ID = a12.LOC_ID and
a11.LBR_DEPT_ID = a13.LBR_DEPT_ID and
a11.LOC_ID = a14.LOC_ID
and (a12.MOB_STATUS in ('STANDARD')
and a11.CALNDR_DT = To_Date('2004-01-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and a11.CLASS_SUBCLASS_ID in (110012)
and (a11.SKU_ID NOT IN(SELECT Z.SKU_ID FROM U29PRPA.MOB_SKU_DEF Z WHERE Z.SKU_TYP = 'PRODUCT' AND Z.Include_Exclude = 'E' AND Z.Category = 'AMPS' AND Z.RCD_START_DT <= (SYSDATE) AND NVL(Z.RCD_END_DT,SYSDATE+1) > SYSDATE)
or a11.SKU_ID IN(SELECT S.Sku_ID
FROM U29PRPA.MOB_SKU_DEF S WHERE S.SKU_TYP = 'PRODUCT'AND S.Include_Exclude = 'I'
AND S.Category = 'AMPS' AND S.RCD_START_DT <= (SYSDATE)AND NVL(S.RCD_END_DT,SYSDATE+1) > SYSDATE)))
group by a11.LBR_DEPT_ID,
a13.LBR_DEPT_NM,
a11.LOC_ID,
a14.LOC_NM,
a11.CALNDR_DT


3rd report
Tables Accessed:
BPS_FIN_LOC_SKU_DT
LU_TM_DAY
MOB_SAMEDAY_DATA
MOB_LOC_DEF
LU_MERCH_LBR_DEPT
LU_GEOG_LOC


SQL Statements:

Pass0 - Duration: 0:01:57.48
create table ZZT4S0205KEMD000 nologging as
select a11.LOC_ID LOC_ID,
a11.LBR_DEPT_ID LBR_DEPT_ID,
a11.CALNDR_DT CALNDR_DT,
sum(a11.UNIT) WJXBFS1
from BPS_FIN_LOC_SKU_DT a11,
U29PRPA.MOB_LOC_DEF a12
where a11.LOC_ID = a12.LOC_ID
and (a12.MOB_STATUS in ('STANDARD')
and a11.CALNDR_DT = To_Date('2004-01-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and a11.CLASS_SUBCLASS_ID in (110012)
and (a11.SKU_ID NOT IN(SELECT Z.SKU_ID FROM U29PRPA.MOB_SKU_DEF Z WHERE Z.SKU_TYP = 'PRODUCT' AND Z.Include_Exclude = 'E' AND Z.Category = 'AMPS' AND Z.RCD_START_DT <= (SYSDATE) AND NVL(Z.RCD_END_DT,SYSDATE+1) > SYSDATE)
or a11.SKU_ID IN(SELECT S.Sku_ID
FROM U29PRPA.MOB_SKU_DEF S WHERE S.SKU_TYP = 'PRODUCT'AND S.Include_Exclude = 'I'
AND S.Category = 'AMPS' AND S.RCD_START_DT <= (SYSDATE)AND NVL(S.RCD_END_DT,SYSDATE+1) > SYSDATE)))
group by a11.LOC_ID,
a11.LBR_DEPT_ID,
a11.CALNDR_DT

Pass1 - Duration: 0:00:05.08
create table ZZT4S0205KEMD001 nologging as
select a11.LOC_ID LOC_ID,
a13.LBR_DEPT_ID LBR_DEPT_ID,
a12.CALNDR_DT CALNDR_DT,
count(a11.SCENARIO_ID) Same_Day_4_Avg
from U29PRPA.MOB_SAMEDAY_DATA a11,
LU_TM_DAY a12,
LU_MERCH_LBR_DEPT a13,
U29PRPA.MOB_LOC_DEF a14
where a11.LOC_ID = a14.LOC_ID
and (a14.RCD_START_DT <= (sysdate)
and NVL(a14.RCD_END_DT , SYSDATE+1) >= SYSDATE
and a11.SCENARIO_ID between (Select MAX(SCENARIO_ID) -3 from U29PRPA.MOB_SAMEDAY_DATA) and (Select MAX(SCENARIO_ID) from U29PRPA.MOB_SAMEDAY_DATA)
and a14.MOB_STATUS in ('STANDARD')
and a12.CALNDR_DT = (select MAX(CALNDR_DT) from PROD.LU_TM_DAY where FISC_MTH_ID = (select FISC_MTH_ID from PROD.LU_TM_DAY where calndr_dt = trunc(sysdate-30))))
group by a11.LOC_ID,
a13.LBR_DEPT_ID,
a12.CALNDR_DT

Pass2 - Duration: 0:00:00.30
create table ZZT4S0205KEOJ002 nologging as
select pa1.LOC_ID LOC_ID,
pa1.LBR_DEPT_ID LBR_DEPT_ID,
pa1.CALNDR_DT CALNDR_DT
from ZZT4S0205KEMD000 pa1

Pass3 - Duration: 0:00:00.82
insert into ZZT4S0205KEOJ002
select pa2.LOC_ID LOC_ID,
pa2.LBR_DEPT_ID LBR_DEPT_ID,
pa2.CALNDR_DT CALNDR_DT
from ZZT4S0205KEMD001 pa2

Pass4 - Duration: 0:00:01.12
create table ZZT4S0205KEOD003 nologging as
select distinct pa3.LOC_ID LOC_ID,
pa3.LBR_DEPT_ID LBR_DEPT_ID,
pa3.CALNDR_DT CALNDR_DT
from ZZT4S0205KEOJ002 pa3

Pass5 - Duration: 0:00:02.49
select distinct pa4.LBR_DEPT_ID LBR_DEPT_ID,
a11.LBR_DEPT_NM LBR_DEPT_NM,
pa4.LOC_ID LOC_ID,
a12.LOC_NM LOC_NM,
pa4.CALNDR_DT CALNDR_DT,
pa1.WJXBFS1 WJXBFS1,
pa2.Same_Day_4_Avg Same_Day_4_Avg
from ZZT4S0205KEOD003 pa4,
ZZT4S0205KEMD000 pa1,
ZZT4S0205KEMD001 pa2,
LU_MERCH_LBR_DEPT a11,
PROD.LU_GEOG_LOC a12
where pa4.CALNDR_DT = pa1.CALNDR_DT (+) and
pa4.LBR_DEPT_ID = pa1.LBR_DEPT_ID (+) and
pa4.LOC_ID = pa1.LOC_ID (+) and
pa4.CALNDR_DT = pa2.CALNDR_DT (+) and
pa4.LBR_DEPT_ID = pa2.LBR_DEPT_ID (+) and
pa4.LOC_ID = pa2.LOC_ID (+) and
pa4.LBR_DEPT_ID = a11.LBR_DEPT_ID and
pa4.LOC_ID = a12.LOC_ID

Pass6 - Duration: 0:00:01.01
drop table ZZT4S0205KEMD000

Pass7 - Duration: 0:00:00.37
drop table ZZT4S0205KEMD001

Pass8 - Duration: 0:00:00.30
drop table ZZT4S0205KEOJ002

Pass9 - Duration: 0:00:00.20
drop table ZZT4S0205KEOD003


i would appreciate any clues, help .thanks
su

 

[nlim]

Where is the cross join you mentioned? I can't find it in the SQL you posted. The SQL statements you posted should produce exactly the same results for the 3 reports. Look carefully at the data for the first report, and see if the metric values are the same as the 3rd report.

The empty metric values show up because some combinations of LBR_DEPT_ID,LBR_DEPT_NM,LOC_ID,LOC_NM,CALNDR_DT have data for one metric but not the other.

 

[suchitha]

hi nlim
here is sql that might better explain my situation. the cross join is between the tables LU_TM_DAY and MOB_SAMEDAY_DATA

I am posting the sql generated from three simple reports. The first report would contain just the first metric and just one attribute from 1 frequently used dimension. The second report would contain the same attribute but
with the second metric instead of the first. The last report will contain both metrics needed to generate the problem sql.

1st report
Tables Accessed:
LU_TM_DAY
MOB_SAMEDAY_DATA
MOB_LOC_DEF
LU_GEOG_LOC


SQL Statements:

Pass0 - Duration: 0:00:03.14
select a11.LOC_ID LOC_ID,
a14.LOC_NM LOC_NM,
a12.CALNDR_DT CALNDR_DT,
count(a11.SCENARIO_ID) Same_Day_4_Avg
from U29PRPA.MOB_SAMEDAY_DATA a11,
LU_TM_DAY a12,
U29PRPA.MOB_LOC_DEF a13,
PROD.LU_GEOG_LOC a14
where a11.LOC_ID = a13.LOC_ID and
a11.LOC_ID = a14.LOC_ID
and (a13.RCD_START_DT <= (sysdate)
and NVL(a13.RCD_END_DT , SYSDATE+1) >= SYSDATE
and a11.SCENARIO_ID between (Select MAX(SCENARIO_ID) -3 from U29PRPA.MOB_SAMEDAY_DATA) and (Select MAX(SCENARIO_ID) from U29PRPA.MOB_SAMEDAY_DATA)
and a13.MOB_STATUS in ('STANDARD')
and a12.CALNDR_DT = (select MAX(CALNDR_DT) from PROD.LU_TM_DAY where FISC_MTH_ID = (select FISC_MTH_ID from PROD.LU_TM_DAY where calndr_dt = trunc(sysdate-30))))
group by a11.LOC_ID,
a14.LOC_NM,
a12.CALNDR_DT

2nd report
Tables Accessed:
BPS_FIN_LOC_SKU_DT
MOB_LOC_DEF
LU_GEOG_LOC


SQL Statements:

Pass0 - Duration: 0:01:54.10
select a11.LOC_ID LOC_ID,
a13.LOC_NM LOC_NM,
a11.CALNDR_DT CALNDR_DT,
sum(a11.UNIT) WJXBFS1
from BPS_FIN_LOC_SKU_DT a11,
U29PRPA.MOB_LOC_DEF a12,
PROD.LU_GEOG_LOC a13
where a11.LOC_ID = a12.LOC_ID and
a11.LOC_ID = a13.LOC_ID
and (a12.MOB_STATUS in ('STANDARD')
and a11.CALNDR_DT = To_Date('2004-01-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and a11.CLASS_SUBCLASS_ID in (110012)
and (a11.SKU_ID NOT IN(SELECT Z.SKU_ID FROM U29PRPA.MOB_SKU_DEF Z WHERE Z.SKU_TYP = 'PRODUCT' AND Z.Include_Exclude = 'E' AND Z.Category = 'AMPS' AND Z.RCD_START_DT <= (SYSDATE) AND NVL(Z.RCD_END_DT,SYSDATE+1) > SYSDATE)
or a11.SKU_ID IN(SELECT S.Sku_ID
FROM U29PRPA.MOB_SKU_DEF S WHERE S.SKU_TYP = 'PRODUCT'AND S.Include_Exclude = 'I'
AND S.Category = 'AMPS' AND S.RCD_START_DT <= (SYSDATE)AND NVL(S.RCD_END_DT,SYSDATE+1) > SYSDATE)))
group by a11.LOC_ID,
a13.LOC_NM,
a11.CALNDR_DT

3rd report
Tables Accessed:
BPS_FIN_LOC_SKU_DT
LU_TM_DAY
MOB_SAMEDAY_DATA
MOB_LOC_DEF
LU_GEOG_LOC


SQL Statements:

Pass0 - Duration: 0:02:00.64
create table ZZT4S0605RUMD000 nologging as
select a11.LOC_ID LOC_ID,
a11.CALNDR_DT CALNDR_DT,
sum(a11.UNIT) WJXBFS1
from BPS_FIN_LOC_SKU_DT a11,
U29PRPA.MOB_LOC_DEF a12
where a11.LOC_ID = a12.LOC_ID
and (a12.MOB_STATUS in ('STANDARD')
and a11.CALNDR_DT = To_Date('2004-01-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and a11.CLASS_SUBCLASS_ID in (110012)
and (a11.SKU_ID NOT IN(SELECT Z.SKU_ID FROM U29PRPA.MOB_SKU_DEF Z WHERE Z.SKU_TYP = 'PRODUCT' AND Z.Include_Exclude = 'E' AND Z.Category = 'AMPS' AND Z.RCD_START_DT <= (SYSDATE) AND NVL(Z.RCD_END_DT,SYSDATE+1) > SYSDATE)
or a11.SKU_ID IN(SELECT S.Sku_ID
FROM U29PRPA.MOB_SKU_DEF S WHERE S.SKU_TYP = 'PRODUCT'AND S.Include_Exclude = 'I'
AND S.Category = 'AMPS' AND S.RCD_START_DT <= (SYSDATE)AND NVL(S.RCD_END_DT,SYSDATE+1) > SYSDATE)))
group by a11.LOC_ID,
a11.CALNDR_DT

Pass1 - Duration: 0:00:03.16
create table ZZT4S0605RUMD001 nologging as
select a11.LOC_ID LOC_ID,
a12.CALNDR_DT CALNDR_DT,
count(a11.SCENARIO_ID) Same_Day_4_Avg
from U29PRPA.MOB_SAMEDAY_DATA a11,
LU_TM_DAY a12,
U29PRPA.MOB_LOC_DEF a13
where a11.LOC_ID = a13.LOC_ID
and (a13.RCD_START_DT <= (sysdate)
and NVL(a13.RCD_END_DT , SYSDATE+1) >= SYSDATE
and a11.SCENARIO_ID between (Select MAX(SCENARIO_ID) -3 from U29PRPA.MOB_SAMEDAY_DATA) and (Select MAX(SCENARIO_ID) from U29PRPA.MOB_SAMEDAY_DATA)
and a13.MOB_STATUS in ('STANDARD')
and a12.CALNDR_DT = (select MAX(CALNDR_DT) from PROD.LU_TM_DAY where FISC_MTH_ID = (select FISC_MTH_ID from PROD.LU_TM_DAY where calndr_dt = trunc(sysdate-30))))
group by a11.LOC_ID,
a12.CALNDR_DT

Pass2 - Duration: 0:00:00.20
create table ZZT4S0605RUOJ002 nologging as
select pa1.LOC_ID LOC_ID,
pa1.CALNDR_DT CALNDR_DT
from ZZT4S0605RUMD000 pa1

Pass3 - Duration: 0:00:00.04
insert into ZZT4S0605RUOJ002
select pa2.LOC_ID LOC_ID,
pa2.CALNDR_DT CALNDR_DT
from ZZT4S0605RUMD001 pa2

Pass4 - Duration: 0:00:00.20
create table ZZT4S0605RUOD003 nologging as
select distinct pa3.LOC_ID LOC_ID,
pa3.CALNDR_DT CALNDR_DT
from ZZT4S0605RUOJ002 pa3

Pass5 - Duration: 0:00:00.14
select distinct pa4.LOC_ID LOC_ID,
a11.LOC_NM LOC_NM,
pa4.CALNDR_DT CALNDR_DT,
pa1.WJXBFS1 WJXBFS1,
pa2.Same_Day_4_Avg Same_Day_4_Avg
from ZZT4S0605RUOD003 pa4,
ZZT4S0605RUMD000 pa1,
ZZT4S0605RUMD001 pa2,
PROD.LU_GEOG_LOC a11
where pa4.CALNDR_DT = pa1.CALNDR_DT (+) and
pa4.LOC_ID = pa1.LOC_ID (+) and
pa4.CALNDR_DT = pa2.CALNDR_DT (+) and
pa4.LOC_ID = pa2.LOC_ID (+) and
pa4.LOC_ID = a11.LOC_ID

Pass6 - Duration: 0:00:00.15
drop table ZZT4S0605RUMD000

Pass7 - Duration: 0:00:00.18
drop table ZZT4S0605RUMD001

Pass8 - Duration: 0:00:00.20
drop table ZZT4S0605RUOJ002

Pass9 - Duration: 0:00:00.16
drop table ZZT4S0605RUOD003


in the VLDB properties , i set the cartesian join setting not to execute if there is a cartesian join.


suchitha

 

[nlim]

yes, I see it now. The problem is there is no linkage between LU_TM_DAY and MOB_SAMEDAY_DATA. You should be getting a terrible cross join in the first report. It leads me to believe that there is a missing attribute link.

Here's what I would suggest. Open up your date attribute, (the one with LU_TM_DAY.CALNDR_DT as ID). Click on ID form modify, see if MOB_SAMEDAY_DATA is included as one of the source tables. If it isn't, add it. Update schema, clear cache and run report 1. If calndr_dt is in MOB_SAMEDAY_DATA, then there should be a &quot;a11.CALNDR_DT= a12.CALNDR_DT&quot; in the where clause to prevent the cross join.

If MOB_SAMEDAY_DATA is in the ID form already, let us know and we'll progress from there.

 

[suchitha]

hi nlim
mob_same_day is not included as a source table in the attribute. i will add and see if it runs. thanks for your help. will keep you updated
su

 

[suchitha]

hi nlim
stupid question but can you refresh my memory on how to add MOB_SAMEDAY_DATA as a source table in ID form.
thanks
su

 

[nlim]

I'm pretty sure that CALNDR_DT is not in MOB_SAMEDAY_DATA table, right? If it is, just check the box next to the table name.

If not, then you need to find out what the column is that represents date in MOB_SAMEDAY_DATA table. Let's say it is Date_column. All you need to do is under the ID form, click on new expression (not new form), select MOB_SAME_DATA and pick the Date_column as the expression.

You should be all set then. Update schema, clear cache and run report 1. The join will be CALNDR_DT=Date_Column.

 

[suchitha]

hi nlim
i did that and updated the schema. here is what i have now

I am posting the sql generated from three simple reports. The first report would contain just the first metric and just one attribute from 1 frequently used dimension. The second report would contain the same attribute but
with the second metric instead of the first. The last report will contain both metrics needed to generate the problem sql.

the first report returns no data and in the third report ... there is no data under that problem metric column. there is no cross join now.

1st report
LU_TM_DAY
MOB_SAMEDAY_DATA
MOB_LOC_DEF
LU_GEOG_LOC


SQL Statements:

Pass0 - Duration: 0:00:00.18
select a11.LOC_ID LOC_ID,
a14.LOC_NM LOC_NM,
a11.CALL_DT CALNDR_DT,
count(a11.SCENARIO_ID) Same_Day_4_Avg
from U29PRPA.MOB_SAMEDAY_DATA a11,
LU_TM_DAY a12,
U29PRPA.MOB_LOC_DEF a13,
PROD.LU_GEOG_LOC a14
where a11.CALL_DT = a12.CALNDR_DT and
a11.LOC_ID = a13.LOC_ID and
a11.LOC_ID = a14.LOC_ID
and (a13.RCD_START_DT <= (sysdate)
and NVL(a13.RCD_END_DT , SYSDATE+1) >= SYSDATE
and a11.SCENARIO_ID between (Select MAX(SCENARIO_ID) -3 from U29PRPA.MOB_SAMEDAY_DATA) and (Select MAX(SCENARIO_ID) from U29PRPA.MOB_SAMEDAY_DATA)
and a13.MOB_STATUS in ('STANDARD')
and a11.CALL_DT = (select MAX(CALNDR_DT) from PROD.LU_TM_DAY where FISC_MTH_ID = (select FISC_MTH_ID from PROD.LU_TM_DAY where calndr_dt = trunc(sysdate-30))))
group by a11.LOC_ID,
a14.LOC_NM,
a11.CALL_DT

2nd report
Tables Accessed:
BPS_FIN_LOC_SKU_DT
MOB_LOC_DEF
LU_GEOG_LOC


SQL Statements:

Pass0 - Duration: 0:01:08.33
select a11.LOC_ID LOC_ID,
a13.LOC_NM LOC_NM,
a11.CALNDR_DT CALNDR_DT,
sum(a11.UNIT) WJXBFS1
from BPS_FIN_LOC_SKU_DT a11,
U29PRPA.MOB_LOC_DEF a12,
PROD.LU_GEOG_LOC a13
where a11.LOC_ID = a12.LOC_ID and
a11.LOC_ID = a13.LOC_ID
and (a12.MOB_STATUS in ('STANDARD')
and a11.CALNDR_DT = To_Date('2004-01-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and a11.CLASS_SUBCLASS_ID in (110012)
and (a11.SKU_ID NOT IN(SELECT Z.SKU_ID FROM U29PRPA.MOB_SKU_DEF Z WHERE Z.SKU_TYP = 'PRODUCT' AND Z.Include_Exclude = 'E' AND Z.Category = 'AMPS' AND Z.RCD_START_DT <= (SYSDATE) AND NVL(Z.RCD_END_DT,SYSDATE+1) > SYSDATE)
or a11.SKU_ID IN(SELECT S.Sku_ID
FROM U29PRPA.MOB_SKU_DEF S WHERE S.SKU_TYP = 'PRODUCT'AND S.Include_Exclude = 'I'
AND S.Category = 'AMPS' AND S.RCD_START_DT <= (SYSDATE)AND NVL(S.RCD_END_DT,SYSDATE+1) > SYSDATE)))
group by a11.LOC_ID,
a13.LOC_NM,
a11.CALNDR_DT


3rd report
Tables Accessed:
BPS_FIN_LOC_SKU_DT
LU_TM_DAY
MOB_SAMEDAY_DATA
MOB_LOC_DEF
LU_GEOG_LOC


SQL Statements:

Pass0 - Duration: 0:01:10.07
create table ZZT4S04069IMD000 nologging as
select a11.LOC_ID LOC_ID,
a11.CALNDR_DT CALNDR_DT,
sum(a11.UNIT) WJXBFS1
from BPS_FIN_LOC_SKU_DT a11,
U29PRPA.MOB_LOC_DEF a12
where a11.LOC_ID = a12.LOC_ID
and (a12.MOB_STATUS in ('STANDARD')
and a11.CALNDR_DT = To_Date('2004-01-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and a11.CLASS_SUBCLASS_ID in (110012)
and (a11.SKU_ID NOT IN(SELECT Z.SKU_ID FROM U29PRPA.MOB_SKU_DEF Z WHERE Z.SKU_TYP = 'PRODUCT' AND Z.Include_Exclude = 'E' AND Z.Category = 'AMPS' AND Z.RCD_START_DT <= (SYSDATE) AND NVL(Z.RCD_END_DT,SYSDATE+1) > SYSDATE)
or a11.SKU_ID IN(SELECT S.Sku_ID
FROM U29PRPA.MOB_SKU_DEF S WHERE S.SKU_TYP = 'PRODUCT'AND S.Include_Exclude = 'I'
AND S.Category = 'AMPS' AND S.RCD_START_DT <= (SYSDATE)AND NVL(S.RCD_END_DT,SYSDATE+1) > SYSDATE)))
group by a11.LOC_ID,
a11.CALNDR_DT

Pass1 - Duration: 0:00:00.22
create table ZZT4S04069IMD001 nologging as
select a11.LOC_ID LOC_ID,
a11.CALL_DT CALNDR_DT,
count(a11.SCENARIO_ID) Same_Day_4_Avg
from U29PRPA.MOB_SAMEDAY_DATA a11,
LU_TM_DAY a12,
U29PRPA.MOB_LOC_DEF a13
where a11.CALL_DT = a12.CALNDR_DT and
a11.LOC_ID = a13.LOC_ID
and (a13.RCD_START_DT <= (sysdate)
and NVL(a13.RCD_END_DT , SYSDATE+1) >= SYSDATE
and a11.SCENARIO_ID between (Select MAX(SCENARIO_ID) -3 from U29PRPA.MOB_SAMEDAY_DATA) and (Select MAX(SCENARIO_ID) from U29PRPA.MOB_SAMEDAY_DATA)
and a13.MOB_STATUS in ('STANDARD')
and a11.CALL_DT = (select MAX(CALNDR_DT) from PROD.LU_TM_DAY where FISC_MTH_ID = (select FISC_MTH_ID from PROD.LU_TM_DAY where calndr_dt = trunc(sysdate-30))))
group by a11.LOC_ID,
a11.CALL_DT

Pass2 - Duration: 0:00:00.10
create table ZZT4S04069IOJ002 nologging as
select pa1.LOC_ID LOC_ID,
pa1.CALNDR_DT CALNDR_DT
from ZZT4S04069IMD000 pa1

Pass3 - Duration: 0:00:00.02
insert into ZZT4S04069IOJ002
select pa2.LOC_ID LOC_ID,
pa2.CALNDR_DT CALNDR_DT
from ZZT4S04069IMD001 pa2

Pass4 - Duration: 0:00:00.10
create table ZZT4S04069IOD003 nologging as
select distinct pa3.LOC_ID LOC_ID,
pa3.CALNDR_DT CALNDR_DT
from ZZT4S04069IOJ002 pa3

Pass5 - Duration: 0:00:00.11
select distinct pa4.LOC_ID LOC_ID,
a11.LOC_NM LOC_NM,
pa4.CALNDR_DT CALNDR_DT,
pa1.WJXBFS1 WJXBFS1,
pa2.Same_Day_4_Avg Same_Day_4_Avg
from ZZT4S04069IOD003 pa4,
ZZT4S04069IMD000 pa1,
ZZT4S04069IMD001 pa2,
PROD.LU_GEOG_LOC a11
where pa4.CALNDR_DT = pa1.CALNDR_DT (+) and
pa4.LOC_ID = pa1.LOC_ID (+) and
pa4.CALNDR_DT = pa2.CALNDR_DT (+) and
pa4.LOC_ID = pa2.LOC_ID (+) and
pa4.LOC_ID = a11.LOC_ID

Pass6 - Duration: 0:00:00.12
drop table ZZT4S04069IMD000

Pass7 - Duration: 0:00:00.10
drop table ZZT4S04069IMD001

Pass8 - Duration: 0:00:00.06
drop table ZZT4S04069IOJ002

Pass9 - Duration: 0:00:00.06
drop table ZZT4S04069IOD003


any ideas ...
su

 

[nlim]

I'm glad your cross join issue is fixed. You will need to get the first report coming back with data.

Since I am not a consultant or employee of your company, I'm afraid that I cannot help you further. .

I would recommend some training or consulting to move your project along. Good luck.

 

[suchitha]

Thanks for all your help.

 

[suchitha]

hi nlim
i was wondering if you were interested in doing some off site consulting. the pay is pretty good. Please let me know if you are interested and we can go ahead from there.
the work would be pretty similar to the cross join problem that you handled.
su

 

[suchitha]

hi again nlim
you can contact me at unique_seller@yahoo.com.
thanks
su

 

[suchitha]

WE have table which has two SKU's,
one is a product SKU and the other is market SKU.
now both these SKU columns are exactly same, the only
differentiating factor between these is an indicator column which is set to 'Y' for the market SKU. now i need to make an attribute where i choose the market SKU and set the indicator to 'Y'

i am essentially selecting the market sku column where indicator = 'y' . how do i make such an attribute?
Any ideas and help would be greatly appreciated
Thanks
SU

 

[entaroadun]

Are these two different columns on the same table that have the same contents? Or do the two attributes share the same column on the same table, except the market SKU should only be for rows where the indicator = 'Y'?

If you want to populate an attribute with a subset of a table, then you will need to create a view that filters out the rows you want. You cannot define an attribute in MSTR with a built-in filter.

 

[suchitha]

These are two different columns on the same table that have the same contents.

i was thinking of defining the market sku as
ApplySimple("Case when #0 = 'Y' then #1 end", IND_COL,
SKU_MKT)

but don't think it will work. Any other ideas would greatly be appreciated.

 

[entaroadun]

That won't work. You need a view. Sorry.

 

[MSTRDUDE]

DId anyone have to implement custom functions and procedures in their reports? If yes, does microstrategy have the capability to pass paramaters to these functions. Has anyone does this before? Using the pre and
post VLDB properties, we can certainly get it to show in the report SQL but we want to be able to implement these functions inside that report so that we can use the custom functions logic to form a part of the report.

The logic of some of these functions is very complex, so the only way to implement these reports would be to use custom SQL.
Any suggestions/pointers would be extremely helpful