Report Error

[Leo1278]

Hi All,

I have this interesting situation where I do not get values displayed on my report. I get something like...
-----------------------------------------------------------
Item#(WhseCD) Cost Purchases Sales
1 ABC 0 10.89 0
2 DEF 0 89.89 0
3 GHI 0 32.34 0
-----------------------------------------------------------

The below is the SQL generated....

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

Pass0 - Duration: 0:00:00.06
declare global temporary table session.ZZT1Y03146CMD000(
Store SMALLINT,
ITMNBR INTEGER,
WHSECD SMALLINT,
WJXBFS1 DOUBLE,
WJXBFS2 DOUBLE) on commit preserve rows

Pass1 - Duration: 0:00:07.71
insert into session.ZZT1Y03146CMD000
select a11.STORNBR Store,
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 700 and 705
and a11.GLDEPTCD between '03' and '03'
and a11.BUSDY between '2004-11-14' and '2004-12-11')
group by a11.STORNBR,
a11.ITMNBR,
a11.WHSECD

Pass2 - Duration: 0:00:00.04
declare global temporary table session.ZZT1Y03146CMD001(
Store SMALLINT,
ITMNBR INTEGER,
WHSECD SMALLINT,
WJXBFS1 DOUBLE) on commit preserve rows

Pass3 - Duration: 0:00:18.31
insert into session.ZZT1Y03146CMD001
select a11.STORNBR Store,
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 700 and 705
and a12.GLDEPTCD between '03' and '03'
and a11.BUSDY between '2004-11-14' and '2004-12-11'
and a11.SUPLCD in ('N'))
group by a11.STORNBR,
a11.ITMNBR,
a11.WHSECD

Pass4 - Duration: 0:00:00.03
declare global temporary table session.ZZT1Y03146CMD002(
Store SMALLINT,
ITMNBR INTEGER,
WHSECD SMALLINT,
WJXBFS1 DOUBLE) on commit preserve rows

Pass5 - Duration: 0:00:01.07
insert into session.ZZT1Y03146CMD002
select a11.STORNBR Store,
a11.ITMNBR ITMNBR,
a11.WHSECD WHSECD,
(2.0 * 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 700 and 705
and a11.BUSDY between '2004-11-14' and '2004-12-11'
and a11.SUPLCD in ('N')
and a12.STORODERTYPCD = 'C')
group by a11.STORNBR,
a11.ITMNBR,
a11.WHSECD

Pass6 - Duration: 0:00:00.04
declare global temporary table session.ZZT1Y03146COJ003(
Store SMALLINT,
ITMNBR INTEGER,
WHSECD SMALLINT) on commit preserve rows

Pass7 - Duration: 0:00:00.07
insert into session.ZZT1Y03146COJ003
select pa1.Store Store,
pa1.ITMNBR ITMNBR,
pa1.WHSECD WHSECD
from session.ZZT1Y03146CMD000 pa1

Pass8 - Duration: 0:00:00.04
insert into session.ZZT1Y03146COJ003
select pa2.Store Store,
pa2.ITMNBR ITMNBR,
pa2.WHSECD WHSECD
from session.ZZT1Y03146CMD001 pa2

Pass9 - Duration: 0:00:00.03
declare global temporary table session.ZZT1Y03146COD004(
Store SMALLINT,
ITMNBR INTEGER,
WHSECD SMALLINT) on commit preserve rows

Pass10 - Duration: 0:00:00.09
insert into session.ZZT1Y03146COD004
select distinct pa4.Store Store,
pa4.ITMNBR ITMNBR,
pa4.WHSECD WHSECD
from session.ZZT1Y03146COJ003 pa4

Pass11 - Duration: 0:00:00.18
select distinct pa5.ITMNBR ITMNBR,
pa5.WHSECD WHSECD,
a11.ITM ITM,
pa5.Store Store,
a12.STORNAME STORNAME,
pa1.WJXBFS1 WJXBFS1,
(VALUE(pa2.WJXBFS1, 0) - VALUE(pa3.WJXBFS1, 0)) WJXBFS2,
pa1.WJXBFS2 WJXBFS3
from session.ZZT1Y03146COD004 pa5
left outer join session.ZZT1Y03146CMD000 pa1
on pa5.ITMNBR = pa1.ITMNBR and
pa5.Store = pa1.Store and
pa5.WHSECD = pa1.WHSECD
left outer join session.ZZT1Y03146CMD001 pa2
on pa5.ITMNBR = pa2.ITMNBR and
pa5.Store = pa2.Store and
pa5.WHSECD = pa2.WHSECD
left outer join session.ZZT1Y03146CMD002 pa3
on pa5.ITMNBR = pa3.ITMNBR and
pa5.Store = pa3.Store and
pa5.WHSECD = pa3.WHSECD
join DWADMV.ITM a11
on pa5.ITMNBR = a11.ITMNBR and
pa5.WHSECD = a11.WHSECD
join DWADMV.STOR a12
on pa5.Store = a12.STORNBR

Pass12 - Duration: 0:00:00.06
drop table session.ZZT1Y03146CMD000

Pass13 - Duration: 0:00:00.04
drop table session.ZZT1Y03146CMD001

Pass14 - Duration: 0:00:00.06
drop table session.ZZT1Y03146CMD002

Pass15 - Duration: 0:00:00.04
drop table session.ZZT1Y03146COJ003

Pass16 - Duration: 0:00:00.09
drop table session.ZZT1Y03146COD004

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

This SQL works for a set of stores and doesnt work for another set of stores. I made sure that I have data for all the stores and for the specific time frame. I also tried to play around with all the metric & Attribute join types in Report Data options. I tried to remove the metric "Purchases" from the report and I get the message "No data returned", as is obvious from the above example.

Can someone please explain me what is happening here. I am working on this for the past 2 days now and I am still looking for an answer.

Please help me understand what is happening and advice me what else should I look into.

Thank you all.

- Leo

 

[Rambo2292468]

Hi Leo,

I guess you using teradata as a backend. Seeing your sql here two suggestions :


1. It looks like in the last pass you have sql with inner join where you data might be getting filter. Try to check on Backend by replacing inner join with left outer or full outer join in the query below:

pa5.WHSECD = pa3.WHSECD
join DWADMV.ITM a11
on pa5.ITMNBR = a11.ITMNBR and
pa5.WHSECD = a11.WHSECD
join DWADMV.STOR a12
on pa5.Store = a12.STORNBR

This may help.

2. I am sure this you must have done though do check teh format of metric is it set to right fomrat with decimal if needed.

Thanks
Rambo

 

[Leo1278]

Hi Rambo..

Thank you for your response. Actually, I am working on DB2 but not teradata.
You observation with the join seem reasonable to me. However, either full/left outer join dint give me any results.

So, ultimately, I removed one metric that is off the "GROSSINVAMT" fact column to simplify the SQL and heres what I got...

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

select a11.ITMNBR ITMNBR,
a11.WHSECD WHSECD,
max(a12.ITM) ITM,
a11.STORNBR Store,
max(a13.STORNAME) STORNAME,
sum(a11.TOTLUNITCOST) WJXBFS1,
(sum(a11.SALEDOLLAR) - sum(a11.TOTLUNITDISCNT)) WJXBFS2
from DWADMV.DYITMSTORSALE a11
join DWADMV.ITM a12
on a11.ITMNBR = a12.ITMNBR and
a11.WHSECD = a12.WHSECD
join DWADMV.STOR a13
on a11.STORNBR = a13.STORNBR
where (a11.STORNBR between 700 and 703
and a11.GLDEPTCD between '07' and '07'
and a11.BUSDY between '2004-11-14' and '2004-12-11')
group by a11.ITMNBR,
a11.WHSECD,
a11.STORNBR
-----------------------------------------------------------

However, that SQL dint really work. Then, I removed
max(a12.ITM) ITM (Basically, changed the attribute display) and the SQL worked like a charm both at the back end and on the report. Well, atleast I got some data. However, I need to validate the data.

Can you explain me why I am getting the result set once I removed ITM?

You explanation will be of great help.

- Leo