May 19, 2017 at 3:39 am
I have a Query which works fine but when I convert that query into a dynamic query, I get NULL values after the union. Please help me find the issue and fix it. I need to pass multiple inputs for one input parameter so I changed the query to Dynamic. Dynamic query with issue:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE OverallItemReport_Select
@StoreNumber varchar(1500),@CYr_FromDate date,@CYr_ToDate date,@LYR_FromDate date,@LYR_ToDate date
AS
BEGIN
SET NOCOUNT ON
declare @sql as nvarchar(4000)
set @sql='select ItemNumber, Variation
,Max(y.QtyPerCase)QtyPerCase
,Max(Case when flg=''ly'' then x.Qty else null end) Qty_ly
,Max(Case when flg=''cy'' then x.Qty else null end) Qty_cy
,Max(Case when flg=''ly'' then x.SellingPrice else null end) SellingPrice_ly
,Max(Case when flg=''cy'' then x.SellingPrice else null end) SellingPrice_cy
,z.y as Cy_Shrink
,r.y as ly_Shrink
from
(select ItemNumber, Variation,sum(Qty)as Qty,SellingPrice,flg from
((select PJ_Matrix_RowDstbn_T.ItemNumber, PJ_Matrix_RowDstbn_T.Variation, Distbn_Date,' + @StoreNumber + ' as Qty, PJ_Item_M.SellingPrice,''cy'' flg
from PJ_Matrix_RowDstbn_T, PJ_Item_M where PJ_Item_M.ItemNumber = PJ_Matrix_RowDstbn_T.ItemNumber
and PJ_Item_M.Variation = PJ_Matrix_RowDstbn_T.Variation and Distbn_Date >= '''+Cast(@CYR_FromDate as varchar(15))+''' and
Distbn_Date <= '''+Cast(@CYR_ToDate as varchar(15))+''' group by PJ_Matrix_RowDstbn_T.ItemNumber, PJ_Matrix_RowDstbn_T.Variation,
Distbn_Date, PJ_Item_M.SellingPrice)
Union
(select ItemNumber, Variation, SplOrd_Date, Sum(Qty) TQty,
Price,''cy'' flg from PJ_Special_Order_T where SplOrd_Date >= '''+Cast(@CYR_FromDate as varchar(15))+''' and SplOrd_Date <= '''+Cast(@CYR_ToDate as varchar(15))+''' group by
ItemNumber, Variation, SplOrd_Date, Price))t where Qty IS NOT NULL group by t.ItemNumber,t.Variation,t.SellingPrice,flg
UNION
select ItemNumber, Variation,sum(Qty)Qty,SellingPrice,flg from
((select PJ_Matrix_RowDstbn_T.ItemNumber,
PJ_Matrix_RowDstbn_T.Variation, Distbn_Date, ' + @StoreNumber + ' as Qty , PJ_Item_M.SellingPrice,''ly'' flg
from PJ_Matrix_RowDstbn_T, PJ_Item_M where
PJ_Item_M.ItemNumber = PJ_Matrix_RowDstbn_T.ItemNumber and
PJ_Item_M.Variation = PJ_Matrix_RowDstbn_T.Variation and Distbn_Date >= '''+Cast(@LYR_FromDate as varchar(15))+'''
and Distbn_Date <= '''+Cast(@LYR_ToDate as varchar(15))+''' group by PJ_Matrix_RowDstbn_T.ItemNumber,
PJ_Matrix_RowDstbn_T.Variation, Distbn_Date, PJ_Item_M.SellingPrice)
Union
(select ItemNumber, Variation, SplOrd_Date, Sum(Qty) TQty, Price,''ly'' flg
from PJ_Special_Order_T where SplOrd_Date >= '''+Cast(@LYR_FromDate as varchar(15))+''' and SplOrd_Date <= '''+Cast(@LYR_ToDate as varchar(15))+'''
group by ItemNumber, Variation, SplOrd_Date, Price))d where Qty IS NOT NULL
group by d.ItemNumber,d.Variation,d.SellingPrice,flg)x,(select QtyPerCase from PJ_Item_M,
PJ_Matrix_RowDstbn_T where PJ_Item_M.ItemNumber=PJ_Matrix_RowDstbn_T.ItemNumber and
PJ_Item_M.Variation=PJ_Matrix_RowDstbn_T.Variation)y,(select (sum(Qty * CAST(Price as decimal(10,2))))
as y from PJ_Shrink_Inventory_T,PJ_Matrix_RowDstbn_T where PJ_Shrink_Inventory_T.ItemNumber=PJ_Matrix_RowDstbn_T.ItemNumber and
PJ_Shrink_Inventory_T.Variation=PJ_Matrix_RowDstbn_T.Variation and Shrink_Date >= '''+Cast(@CYr_FromDate as varchar(15))+''' and
Shrink_Date <= '''+Cast(@CYR_ToDate as varchar(15))+''')as z,(select (sum(Qty * CAST(Price as decimal(10,2)))) as
y from PJ_Shrink_Inventory_T,PJ_Matrix_RowDstbn_T where PJ_Shrink_Inventory_T.ItemNumber=PJ_Matrix_RowDstbn_T.ItemNumber and
PJ_Shrink_Inventory_T.Variation=PJ_Matrix_RowDstbn_T.Variation and Shrink_Date >= '''+Cast(@LYR_FromDate as varchar(15))+''' and
Shrink_Date <= '''+Cast(@LYR_ToDate as varchar(15))+''')as r group by ItemNumber, Variation,z.y,r.y'
print @sql
exec(@sql)
END
GO
May 19, 2017 at 6:31 am
Why did you change the query to dynamic? There's a potentially very good reason...
Here's the query formatted for readability. I'm not prepared to go any further with this, without first correcting some of the many faults with it.select ItemNumber, Variation
,Max(y.QtyPerCase)QtyPerCase
,Max(Case when flg=''ly'' then x.Qty else null end) Qty_ly
,Max(Case when flg=''cy'' then x.Qty else null end) Qty_cy
,Max(Case when flg=''ly'' then x.SellingPrice else null end) SellingPrice_ly
,Max(Case when flg=''cy'' then x.SellingPrice else null end) SellingPrice_cy
,z.y as Cy_Shrink
,r.y as ly_Shrink
from ( -- x
select ItemNumber, Variation,sum(Qty)as Qty,SellingPrice,flg
from ( -- t
(
select PJ_Matrix_RowDstbn_T.ItemNumber, PJ_Matrix_RowDstbn_T.Variation, Distbn_Date,' + @StoreNumber + ' as Qty, PJ_Item_M.SellingPrice,''cy'' flg
from PJ_Matrix_RowDstbn_T, PJ_Item_M
where PJ_Item_M.ItemNumber = PJ_Matrix_RowDstbn_T.ItemNumber
and PJ_Item_M.Variation = PJ_Matrix_RowDstbn_T.Variation
and Distbn_Date >= '''+Cast(@CYR_FromDate as varchar(15))+'''
and Distbn_Date <= '''+Cast(@CYR_ToDate as varchar(15))+'''
group by PJ_Matrix_RowDstbn_T.ItemNumber, PJ_Matrix_RowDstbn_T.Variation, Distbn_Date, PJ_Item_M.SellingPrice
)
Union
(
select ItemNumber, Variation, SplOrd_Date, Sum(Qty) TQty, Price,''cy'' flg
from PJ_Special_Order_T
where SplOrd_Date >= '''+Cast(@CYR_FromDate as varchar(15))+'''
and SplOrd_Date <= '''+Cast(@CYR_ToDate as varchar(15))+'''
group by ItemNumber, Variation, SplOrd_Date, Price
)
) t where Qty IS NOT NULL
group by t.ItemNumber,t.Variation,t.SellingPrice,flg
UNION
select ItemNumber, Variation, sum(Qty) Qty,SellingPrice, flg
from ( -- d
(
select PJ_Matrix_RowDstbn_T.ItemNumber, PJ_Matrix_RowDstbn_T.Variation, Distbn_Date, ' + @StoreNumber + ' as Qty , PJ_Item_M.SellingPrice,''ly'' flg
from PJ_Matrix_RowDstbn_T, PJ_Item_M
where PJ_Item_M.ItemNumber = PJ_Matrix_RowDstbn_T.ItemNumber
and PJ_Item_M.Variation = PJ_Matrix_RowDstbn_T.Variation
and Distbn_Date >= '''+Cast(@LYR_FromDate as varchar(15))+'''
and Distbn_Date <= '''+Cast(@LYR_ToDate as varchar(15))+'''
group by PJ_Matrix_RowDstbn_T.ItemNumber, PJ_Matrix_RowDstbn_T.Variation, Distbn_Date, PJ_Item_M.SellingPrice
)
Union
(
select ItemNumber, Variation, SplOrd_Date, Sum(Qty) TQty, Price,''ly'' flg
from PJ_Special_Order_T
where SplOrd_Date >= '''+Cast(@LYR_FromDate as varchar(15))+''' and SplOrd_Date <= '''+Cast(@LYR_ToDate as varchar(15))+'''
group by ItemNumber, Variation, SplOrd_Date, Price
)
) d where Qty IS NOT NULL
group by d.ItemNumber,d.Variation,d.SellingPrice,flg
) x,
(
select QtyPerCase
from PJ_Item_M, PJ_Matrix_RowDstbn_T
where PJ_Item_M.ItemNumber=PJ_Matrix_RowDstbn_T.ItemNumber
and PJ_Item_M.Variation=PJ_Matrix_RowDstbn_T.Variation
) y,
(
select (sum(Qty * CAST(Price as decimal(10,2)))) as y
from PJ_Shrink_Inventory_T,PJ_Matrix_RowDstbn_T
where PJ_Shrink_Inventory_T.ItemNumber=PJ_Matrix_RowDstbn_T.ItemNumber
and PJ_Shrink_Inventory_T.Variation=PJ_Matrix_RowDstbn_T.Variation
and Shrink_Date >= '''+Cast(@CYr_FromDate as varchar(15))+'''
and Shrink_Date <= '''+Cast(@CYR_ToDate as varchar(15))+'''
) as z,
(
select (sum(Qty * CAST(Price as decimal(10,2)))) as y
from PJ_Shrink_Inventory_T,PJ_Matrix_RowDstbn_T
where PJ_Shrink_Inventory_T.ItemNumber=PJ_Matrix_RowDstbn_T.ItemNumber
and PJ_Shrink_Inventory_T.Variation=PJ_Matrix_RowDstbn_T.Variation
and Shrink_Date >= '''+Cast(@LYR_FromDate as varchar(15))+'''
and Shrink_Date <= '''+Cast(@LYR_ToDate as varchar(15))+'''
)as r
group by ItemNumber, Variation,z.y,r.y
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 19, 2017 at 6:45 am
The @StoreNumber actually is a set of column names in a table. To test the query, I need to pass:
exec OverallItemReport_Select 'sum(cast([1062] as int)) + sum(cast([1070] as int))
+ sum(cast([1093] as int)) + sum(cast([1166] as int)) + sum(cast([1174] as int)) +
sum(cast([1176] as int)) + sum(cast([1177] as int)) + sum(cast([1195] as int)) +
sum(cast([206] as int)) + sum(cast([221] as int)) + sum(cast([226] as int)) +
sum(cast([230] as int)) + sum(cast([231] as int)) + sum(cast([237] as int)) +
sum(cast([240] as int)) + sum(cast([241] as int)) + sum(cast([243] as int)) +
sum(cast([244] as int)) + sum(cast([301] as int)) + sum(cast([302] as int)) +
sum(cast([303] as int)) + sum(cast([304] as int)) + sum(cast([305] as int)) +
sum(cast([306] as int)) + sum(cast([307] as int)) + sum(cast([308] as int)) +
sum(cast([310] as int)) + sum(cast([312] as int)) + sum(cast([313] as int)) +
sum(cast([314] as int)) + sum(cast([315] as int)) + sum(cast([316] as int)) +
sum(cast([318] as int)) + sum(cast([319] as int)) + sum(cast([320] as int)) +
sum(cast([321] as int)) + sum(cast([322] as int)) + sum(cast([323] as int)) +
sum(cast([324] as int)) + sum(cast([326] as int)) + sum(cast([333] as int)) +
sum(cast([780] as int)) + sum(cast([785] as int))','2006-06-06','2006-06-16','2005-05-06','2005-05-16'
This is why I am using Dynamic query. All those numbers are column names which need to be summed up to get the Quantity. I need to test your modified query for the same
May 26, 2017 at 3:18 pm
acmedeepak - Friday, May 19, 2017 6:45 AMThe @StoreNumber actually is a set of column names in a table. To test the query, I need to pass:
exec OverallItemReport_Select 'sum(cast([1062] as int)) + sum(cast([1070] as int))
+ sum(cast([1093] as int)) + sum(cast([1166] as int)) + sum(cast([1174] as int)) +
sum(cast([1176] as int)) + sum(cast([1177] as int)) + sum(cast([1195] as int)) +
sum(cast([206] as int)) + sum(cast([221] as int)) + sum(cast([226] as int)) +
sum(cast([230] as int)) + sum(cast([231] as int)) + sum(cast([237] as int)) +
sum(cast([240] as int)) + sum(cast([241] as int)) + sum(cast([243] as int)) +
sum(cast([244] as int)) + sum(cast([301] as int)) + sum(cast([302] as int)) +
sum(cast([303] as int)) + sum(cast([304] as int)) + sum(cast([305] as int)) +
sum(cast([306] as int)) + sum(cast([307] as int)) + sum(cast([308] as int)) +
sum(cast([310] as int)) + sum(cast([312] as int)) + sum(cast([313] as int)) +
sum(cast([314] as int)) + sum(cast([315] as int)) + sum(cast([316] as int)) +
sum(cast([318] as int)) + sum(cast([319] as int)) + sum(cast([320] as int)) +
sum(cast([321] as int)) + sum(cast([322] as int)) + sum(cast([323] as int)) +
sum(cast([324] as int)) + sum(cast([326] as int)) + sum(cast([333] as int)) +
sum(cast([780] as int)) + sum(cast([785] as int))','2006-06-06','2006-06-16','2005-05-06','2005-05-16'This is why I am using Dynamic query. All those numbers are column names which need to be summed up to get the Quantity. I need to test your modified query for the same
Does this mean each store has its own column(s) in the table? Based on the cast to an int, is it safe to assume you're also storing your sales data as characters?
If this is true, you have a long, hard road ahead of you. I'd highly recommend you push for a refactoring asap. You will burn a ton of hours trying to maintain this and the other problems it causes.
Wes
(A solid design is always preferable to a creative workaround)
May 30, 2017 at 1:50 am
acmedeepak - Friday, May 19, 2017 6:45 AMThe @StoreNumber actually is a set of column names in a table. To test the query, I need to pass:
exec OverallItemReport_Select 'sum(cast([1062] as int)) + sum(cast([1070] as int))
+ sum(cast([1093] as int)) + sum(cast([1166] as int)) + sum(cast([1174] as int)) +
sum(cast([1176] as int)) + sum(cast([1177] as int)) + sum(cast([1195] as int)) +
sum(cast([206] as int)) + sum(cast([221] as int)) + sum(cast([226] as int)) +
sum(cast([230] as int)) + sum(cast([231] as int)) + sum(cast([237] as int)) +
sum(cast([240] as int)) + sum(cast([241] as int)) + sum(cast([243] as int)) +
sum(cast([244] as int)) + sum(cast([301] as int)) + sum(cast([302] as int)) +
sum(cast([303] as int)) + sum(cast([304] as int)) + sum(cast([305] as int)) +
sum(cast([306] as int)) + sum(cast([307] as int)) + sum(cast([308] as int)) +
sum(cast([310] as int)) + sum(cast([312] as int)) + sum(cast([313] as int)) +
sum(cast([314] as int)) + sum(cast([315] as int)) + sum(cast([316] as int)) +
sum(cast([318] as int)) + sum(cast([319] as int)) + sum(cast([320] as int)) +
sum(cast([321] as int)) + sum(cast([322] as int)) + sum(cast([323] as int)) +
sum(cast([324] as int)) + sum(cast([326] as int)) + sum(cast([333] as int)) +
sum(cast([780] as int)) + sum(cast([785] as int))','2006-06-06','2006-06-16','2005-05-06','2005-05-16'This is why I am using Dynamic query. All those numbers are column names which need to be summed up to get the Quantity. I need to test your modified query for the same
This table which has store numbers as column names - is this a permanent table or is it something which has been generated as part of the reporting process?
Either way, as Wes points out, you are welcoming a whole world of pain. Get it normalised. If it's not down to you, then ask someone who has the authority, what they're planning to do when the number of stores reaches a SQL server limit, either row size or column count. Boom!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply