October 9, 2013 at 2:27 pm
How do I run a CTE with multiple CTE queries, each query using the same 2 named parameters (@StartDate and @EndDate), against DB2? I am open to both running the CTE from within a dataset and running it from within a SQL Server Stored Procedure.
October 9, 2013 at 2:41 pm
Just like you would against SQL Server. DB2 also supports CTE expressions.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 9, 2013 at 2:56 pm
The problem is in using the 2 named parameters. It works fine if I use 1 or 2 unnamed parameters (?). If it helps, here is the query:
WITH Main_Query
AS
(
select
hdr.csstor StoreNum,
Store.STRNAM StoreName,
hdr.cscsh CashierNum
from MM750LIB.CSHHDR hdr
left join MM750LIB.TBLSTR Store
on hdr.csstor = Store.STRNUM
where
hdr.CSDATE >= @StartDate
and hdr.CSDATE <= @EndDate
and Store.STRHDO = 'S'
and Store.STCLDT = 0
and Store.STSDAT <> 0
and Store.STSDAT <= @EndDate
group by hdr.CSSTOR, Store.STRNAM, hdr.CSCSH
),
PostVoidTransQty_Query
AS
(
select
CSSTOR StoreNum,
CSCSH CashierNum,
count(CSTRAN) PostVoidQty
from MM750LIB.CSHHDR hdr
where
hdr.CSDATE >= @StartDate
and hdr.CSDATE <= @EndDate
AND hdr.CSTTND <> 0
AND hdr.CSTSTS = 1
Group by hdr.CSSTOR, hdr.CSCSH
),
PreVoidTransQty_Query
AS
(
select
CSSTOR StoreNum,
CSCSH CashierNum,
count(CSTRAN) PreVoidQty
from MM750LIB.CSHHDR
where
CSDATE >= @StartDate
and CSDATE <= @EndDate
AND CSTTND = 0
AND CSTSTS = 1
group by CSSTOR, CSCSH
),
PriceChange_Query
AS
(
select
hdr.csstor StoreNum,
hdr.cscsh CashierNum,
sum(det.csqty) PriceChangeQty,
sum(det.CSEXPR) PriceChangeAmt
from MM750LIB.CSHHDR hdr
left join MM750LIB.CSHDET det
on Hdr.csstor = det.csstor
and Hdr.CSCEN = det.cscen
and hdr.csdate = det.csdate
and hdr.cstran = det.cstran
where
hdr.CSDATE >= @StartDate
and hdr.CSDATE <= @EndDate
and hdr.CSTSTS = '0'
and det.csdsts = '0'
and det.CSDRSN > '0000'
group by hdr.csstor, hdr.cscsh
),
NoSale_Query
AS
(
select
hdr.csstor StoreNum,
hdr.cscsh CashierNum,
count(CSTRAN) NoSaleQty
from MM750LIB.CSHHDR hdr
where
hdr.CSDATE >= @StartDate
and hdr.CSDATE <= @EndDate
and hdr.CSTSTS = '0'
and CSTTND = 0
group by hdr.csstor, hdr.cscsh
),
MiscSKU_Query
AS
(
select
hdr.csstor StoreNum,
hdr.cscsh CashierNum,
sum(det.CSQTY) MiscSKUQty
from MM750LIB.CSHHDR hdr
left join MM750LIB.CSHDET det
on Hdr.csstor = det.csstor
and Hdr.CSCEN = det.cscen
and hdr.csdate = det.csdate
and hdr.cstran = det.cstran
where
hdr.CSDATE >= @StartDate
and hdr.CSDATE <= @EndDate
and hdr.CSTSTS = 0
and det.csdsts = 0
and det.cssku = 1
group by hdr.csstor, hdr.cscsh
),
DiscQtyAmt_Query
AS
(
select
hdr.csstor StoreNum,
hdr.cscsh CashierNum,
sum(det.CSQTY) DiscQty,
sum(det.CSEXDS) DiscAmt
from MM750LIB.CSHHDR hdr
inner join MM750LIB.CSHDET det
on Hdr.csstor = det.csstor
and Hdr.CSCEN = det.cscen
and hdr.csdate = det.csdate
and hdr.cstran = det.cstran
where
hdr.CSDATE >= @StartDate
and hdr.CSDATE <= @EndDate
and hdr.CSTSTS = 0
and det.csdsts = 0
and det.CSEXDS <> 0
group by hdr.csstor, hdr.cscsh
),
PaidQtyAmt_Query
AS
(
select
hdr.csstor StoreNum,
hdr.cscsh CashierNum,
det.CSDTYP PaidType,
sum(det.CSQTY) PaidQty,
sum(det.CSEXPR) PaidAmt
from MM750LIB.CSHHDR hdr
inner join MM750LIB.CSHDET det
on Hdr.csstor = det.csstor
and Hdr.CSCEN = det.cscen
and hdr.csdate = det.csdate
and hdr.cstran = det.cstran
where
hdr.CSDATE >= @StartDate
and hdr.CSDATE <= @EndDate
and hdr.CSTSTS = 0
and det.csdsts = 0
and det.CSDTYP in ('4A', '4B', '4C', '4D', '4E', '4F', '4H', '4L', '4M', '4N', '4O', '4P', '4Q', '4S')
group by hdr.csstor, hdr.cscsh, det.CSDTYP
)
select m.StoreNum, m.StoreName, m.CashierNum, postvoid.PostVoidQty, prevoid.PreVoidQty,
PriceChange.PriceChangeQty, PriceChange.PriceChangeAmt, NoSale.NoSaleQty,
SKU.MiscSKUQty, Disc.DiscQty, Disc.DiscAmt, Paid.PaidType, Paid.PaidQty, Paid.PaidAmt
from Main_Query m
left join PostVoidTransQty_Query postvoid
on m.StoreNum = postvoid.StoreNum
and m.CashierNum = postvoid.CashierNum
Left join PreVoidTransQty_Query prevoid
on m.StoreNum = prevoid.StoreNum
and m.CashierNum = prevoid.CashierNum
left join PriceChange_Query PriceChange
on m.StoreNum = PriceChange.StoreNum
and m.CashierNum = PriceChange.CashierNum
left join NoSale_Query NoSale
on m.StoreNum = NoSale.StoreNum
and m.CashierNum = NoSale.CashierNum
left join MiscSKU_Query SKU
on m.StoreNum = SKU.StoreNum
and m.CashierNum = SKU.CashierNum
left join DiscQtyAmt_Query Disc
on m.StoreNum = Disc.StoreNum
and m.CashierNum = Disc.CashierNum
left join PaidQtyAmt_Query Paid
on m.StoreNum = Paid.StoreNum
and m.CashierNum = Paid.CashierNum
order by m.StoreNum, m.CashierNum
;
October 10, 2013 at 9:44 am
I suspect this may be a db2 question. Has anyone written an SSRS report that passes a query with named parameters to db2?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply