Attribute Relationship Problem?

[Leo1278]

Hello everybody,

I have a compound attribute to get me "Item# & Warehouse Code" of an item. It was all working fine, when on one not-so-fine day, Someone/me messed up with the compound attribute (May have deleted/added a child or parent to this compund attribute). Now, the SQL looks like this...
(Please find more explanation of the problem below the SQL)
-----------------------------------------------------------

declare global temporary table session.ZZMD00(
ITMNBR INTEGER,
WHSECD SMALLINT,
WJXBFS1 DOUBLE,
WJXBFS2 DOUBLE) on commit preserve rows

insert into session.ZZMD00
select a11.ITMNBR ITMNBR,
a11.WHSECD WHSECD,
sum(a11.TOTLUNITCOST) WJXBFS1,
(sum(a11.SALEDOLLAR) - sum(a11.TOTLUNITDISCNT)) WJXBFS2
from DWADMV.DYITMSTORSALE a11
where (a11.STORNBR between 102 and 102
and a11.GLDEPTCD between '07' and '07'
and a11.BUSDY between '2004-11-14' and '2004-12-11')
group by a11.ITMNBR,
a11.WHSECD

declare global temporary table session.ZZMD01(
ITMNBR INTEGER,
WHSECD SMALLINT,
WJXBFS1 DOUBLE) on commit preserve rows

insert into session.ZZMD01
select a11.ITMNBR ITMNBR,
a11.WHSECD WHSECD,
sum(a11.GROSSINVAMT) WJXBFS1
from DWADMV.STORINVCITM a11
join DWADMV.STORINVC a12
on a11.BUSDY = a12.BUSDY and
a11.INVCNBR = a12.INVCNBR
where (a11.STORNBR between 102 and 102
and a12.GLDEPTCD between '07' and '07'
and a11.BUSDY between '2004-11-14' and '2004-12-11'
and a11.SUPLCD in ('N')
and a12.STORODERTYPCD <> 'C')
group by a11.ITMNBR,
a11.WHSECD

declare global temporary table session.ZZMD02(
ITMNBR INTEGER,
WHSECD SMALLINT,
WJXBFS1 DOUBLE) on commit preserve rows

insert into session.ZZMD02
select a11.ITMNBR ITMNBR,
a11.WHSECD WHSECD,
sum(a11.GROSSINVAMT) WJXBFS1
from DWADMV.STORINVCITM a11
join DWADMV.STORINVC a12
on a11.BUSDY = a12.BUSDY and
a11.INVCNBR = a12.INVCNBR
where (a11.STORNBR between 102 and 102
and a12.GLDEPTCD between '07' and '07'
and a11.BUSDY between '2004-11-14' and '2004-12-11'
and a11.SUPLCD in ('N')
and a12.STORODERTYPCD = 'C')
group by a11.ITMNBR,
a11.WHSECD

declare global temporary table session.ZZOJ03(
ITMNBR INTEGER,
WHSECD SMALLINT) on commit preserve rows

insert into session.ZZOJ03
select pa1.ITMNBR ITMNBR,
pa1.WHSECD WHSECD
from session.ZZMD00 pa1

insert into session.ZZOJ03
select pa2.ITMNBR ITMNBR,
pa2.WHSECD WHSECD
from session.ZZMD01 pa2

declare global temporary table session.ZZOD04(
ITMNBR INTEGER,
WHSECD SMALLINT) on commit preserve rows

insert into session.ZZOD04
select distinct pa4.ITMNBR ITMNBR,
pa4.WHSECD WHSECD
from session.ZZOJ03 pa4

select distinct pa5.ITMNBR ITMNBR,
pa5.WHSECD WHSECD,
a13.ITM ITM,
a11.STORNBR Store,
a14.STORNAME STORNAME,
pa1.WJXBFS1 WJXBFS1,
(VALUE(pa2.WJXBFS1, 0) - VALUE(pa3.WJXBFS1, 0)) WJXBFS2,
pa1.WJXBFS2 WJXBFS3
from session.ZZOD04 pa5
left outer join session.ZZMD00 pa1
on pa5.ITMNBR = pa1.ITMNBR and
pa5.WHSECD = pa1.WHSECD
left outer join session.ZZMD01 pa2
on pa5.ITMNBR = pa2.ITMNBR and
pa5.WHSECD = pa2.WHSECD
left outer join session.ZZMD02 pa3
on pa5.ITMNBR = pa3.ITMNBR and
pa5.WHSECD = pa3.WHSECD
join DWADMV.STORINVCITM a11
on pa5.ITMNBR = a11.ITMNBR and
pa5.WHSECD = a11.WHSECD
join DWADMV.STORINVC a12
on a11.BUSDY = a12.BUSDY and
a11.INVCNBR = a12.INVCNBR
join DWADMV.ITM a13
on a11.ITMNBR = a13.ITMNBR and
a11.WHSECD = a13.WHSECD and
pa5.ITMNBR = a13.ITMNBR and
pa5.WHSECD = a13.WHSECD
join DWADMV.STOR a14
on a11.STORNBR = a14.STORNBR


drop table session.ZZMD00

drop table session.ZZMD01

drop table session.ZZMD02

drop table session.ZZOJ03

drop table session.ZZOD04

-----------------------------------------------------------

Now, this is not the original SQL. The original SQL used to look like (I am taking only one pass of the whole SQL to show you how the original SQL used to look like)...

-----------------------------------------------------------

select a11.STORNBR STORNBR,
a11.ITMNBR ITMNBR,
a11.WHSECD WHSECD,
sum(a11.TOTLUNITCOST) WJXBFS1,
(sum(a11.SALEDOLLAR) - sum(a11.TOTLUNITDISCNT)) WJXBFS2
from DWADMV.DYITMSTORSALE a11
where (a11.STORNBR between 102 and 102
and a11.GLDEPTCD between '07' and '07'
and a11.BUSDY between '2004-11-14' and '2004-12-11')
group by a11.STORNBR,
a11.ITMNBR,
a11.WHSECD,
-----------------------------------------------------------

Can someone PLEASE explain me what I should look for and where I should make the necessary changes. I tried to add parents to my compund attribute and tried to change the relationship table so that when the reports are re-executed, they would comeup with "STORNBR" in each of those passes.

Please please please help me with this problem.

Thank you all in advance.

- Leo.

 

[FLB]

I would suspect that either:
- store was unmapped from the DYITMSTORSALE table
- metric dimensionality changed to exclude Store

You should not have any relationship between store, warehouse and item right? [well... maybe a remote connection between warehouse and item but that would be pushing it]