Syntax issue with my Where Clause SQL 2012
In my query I'm using a Case statement to calculate a value for std_price based on two parameters.
My issue is when I try to use std_price in my where. I receive an error saying that it is an invalid column name. So then I tried doing something like this.
However that doesn't work the way I expected either. Basically I need my where to respond as so. If @MaximumPrice is 0 show me everything. If @MaximumPrice has a value "This is a user entered Parm" then only show me records that have a std_price that is < = to the @MaximumPrice.
He is my entire query
CASE WHEN @ShowPrice = 'M'
THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
+ ( ISNULL(@Margin,
0) / 100.0 ) ))
ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
END AS std_price
My issue is when I try to use std_price in my where. I receive an error saying that it is an invalid column name. So then I tried doing something like this.
WHERE ( cs.DIVISION = 'KLL' )
AND @MaximumPrice = 0
OR @MaximumPrice <= cs.a_price
OR @MaximumPrice <= cs.std_cost
However that doesn't work the way I expected either. Basically I need my where to respond as so. If @MaximumPrice is 0 show me everything. If @MaximumPrice has a value "This is a user entered Parm" then only show me records that have a std_price that is < = to the @MaximumPrice.
He is my entire query
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DISTINCT
LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) AS Scale_Style ,
ISNULL(zzxexfvr.stor_file, ' ') AS Img_File_Name ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) AS OTS ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) AS std_cost ,
cs.Size_Ind ,
CASE WHEN @ShowPrice = 'M'
THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
+ ( ISNULL(@Margin,
0) / 100.0 ) ))
ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
END AS std_price ,
cs.Main_Label ,
SUM(cs.AvailQOH) AS AvailQOH ,
SUM(cs.QOH) AS QOH ,
CASE WHEN ( EXISTS ( SELECT *
FROM zzxrangd AS Rg
WHERE ( Rg.style = cs.STYLE )
AND ( Rg.color_code = cs.COLOR_CODE )
AND ( Rg.lbl_code = cs.LBL_CODE ) ) )
THEN ( 'Y' )
ELSE ( 'N' )
END AS RangeComponent ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) AS ETADate ,
cs.Roll_Lbl ,
cs.NeckLabel
FROM ( SELECT a_price ,
SUM(AvailQOH) AS AvailQOH ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
SUM(OTSOpen) AS OTSOpen ,
SUM(OTSPlan) AS OTSPlan ,
SUM(QOH) AS QOH ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation ,
MAX(Roll_Lbl) AS Roll_Lbl
FROM [KLL Cut and Sold OTS Sum - New Rollup]
WHERE ( EndLocation IN ( @Location ) )
GROUP BY a_price ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation
) AS cs
INNER JOIN zzxstylr ON cs.DIVISION = zzxstylr.division
AND cs.STYLE = zzxstylr.style
LEFT OUTER JOIN zzxexfvr ON zzxstylr.pkey = zzxexfvr.FKEY
AND zzxexfvr.CONTEXTID = 'ZZXSTYLR'
AND zzxexfvr.prime_image = 'Y'
WHERE ( cs.DIVISION = 'KLL' )
AND ( @MaxQty = 0 )
OR ( cs.DIVISION = 'KLL' )
AND ( @MinQty = 0 )
GROUP BY LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) ,
ISNULL(zzxexfvr.stor_file, ' ') ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
cs.std_cost ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) ,
cs.Size_Ind ,
CONVERT(DECIMAL(10, 2), cs.a_price) ,
cs.Main_Label ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) ,
cs.Roll_Lbl ,
cs.NeckLabel
HAVING ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) > @MinQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
OR ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) BETWEEN 1
AND @MaxQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
ORDER BY cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
cs.Size_Ind ,
cs.EndLocation;
I think this will work in your case add this to your where clause:
where
(@maximumprice>0 and @maximumPrice<=
CASE WHEN @ShowPrice = 'M'
THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
+ ( ISNULL(@Margin,
0) / 100.0 ) ))
ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
or
@maximumprice=0
)
where
(@maximumprice>0 and @maximumPrice<=
CASE WHEN @ShowPrice = 'M'
THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
+ ( ISNULL(@Margin,
0) / 100.0 ) ))
ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
or
@maximumprice=0
)
It's throwing the following error when I plug it into the query Msg 156, Level 15, State 1, Line 67
Incorrect syntax near the keyword 'or'.
Incorrect syntax near the keyword 'or'.
But for answer your question, try this:
WHERE cs.DIVISION = 'KLL'
AND (@MaxQty = 0 OR @MinQty = 0)
AND (
@MaximumPrice = 0
OR (@ShowPrice = 'M' AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), (cs.std_cost) * ( 1+ (ISNULL(@Margin,0) / 100.0 ))))
OR (@ShowPrice <> 'M' AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price))
)
I've applied code to my where clause and set my @MaximumPrice to Float however it doesn't filter the records.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DISTINCT
LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) AS Scale_Style ,
ISNULL(zzxexfvr.stor_file, ' ') AS Img_File_Name ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) AS OTS ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) AS std_cost ,
cs.Size_Ind ,
CASE WHEN @ShowPrice = 'M'
THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
+ ( ISNULL(@Margin,
0) / 100.0 ) ))
ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
END AS std_price ,
cs.Main_Label ,
SUM(cs.AvailQOH) AS AvailQOH ,
SUM(cs.QOH) AS QOH ,
CASE WHEN ( EXISTS ( SELECT *
FROM zzxrangd AS Rg
WHERE ( Rg.style = cs.STYLE )
AND ( Rg.color_code = cs.COLOR_CODE )
AND ( Rg.lbl_code = cs.LBL_CODE ) ) )
THEN ( 'Y' )
ELSE ( 'N' )
END AS RangeComponent ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) AS ETADate ,
cs.Roll_Lbl ,
cs.NeckLabel
FROM ( SELECT a_price ,
SUM(AvailQOH) AS AvailQOH ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
SUM(OTSOpen) AS OTSOpen ,
SUM(OTSPlan) AS OTSPlan ,
SUM(QOH) AS QOH ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation ,
MAX(Roll_Lbl) AS Roll_Lbl
FROM [KLL Cut and Sold OTS Sum - New Rollup]
WHERE ( EndLocation IN ( @Location ) )
GROUP BY a_price ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation
) AS cs
INNER JOIN zzxstylr ON cs.DIVISION = zzxstylr.division
AND cs.STYLE = zzxstylr.style
LEFT OUTER JOIN zzxexfvr ON zzxstylr.pkey = zzxexfvr.FKEY
AND zzxexfvr.CONTEXTID = 'ZZXSTYLR'
AND zzxexfvr.prime_image = 'Y'
WHERE ( cs.DIVISION = 'KLL' )
AND ( @MaxQty = 0 )
AND ( @MaximumPrice = 0
OR ( @ShowPrice = 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), ( cs.std_cost )
* ( 1 + ( ISNULL(@Margin, 0) / 100.0 ) ))
)
OR ( @ShowPrice <> 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price)
)
)
OR ( cs.DIVISION = 'KLL' )
AND ( @MinQty = 0 )
AND ( @MaximumPrice = 0
OR ( @ShowPrice = 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), ( cs.std_cost )
* ( 1 + ( ISNULL(@Margin, 0) / 100.0 ) ))
)
OR ( @ShowPrice <> 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price)
)
)
GROUP BY LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) ,
ISNULL(zzxexfvr.stor_file, ' ') ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
cs.std_cost ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) ,
cs.Size_Ind ,
CONVERT(DECIMAL(10, 2), cs.a_price) ,
cs.Main_Label ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) ,
cs.Roll_Lbl ,
cs.NeckLabel
HAVING ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) > @MinQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
OR ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) BETWEEN 1
AND @MaxQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
ORDER BY cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
cs.Size_Ind ,
cs.EndLocation;
I've applied code to my where clauseNo, you didn't. Compare your WHERE clause with mine. Can you see the BIG difference? That's why is not returning what you're expected.
Is this not what you sent me?
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DISTINCT
LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) AS Scale_Style ,
ISNULL(zzxexfvr.stor_file, ' ') AS Img_File_Name ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) AS OTS ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) AS std_cost ,
cs.Size_Ind ,
CASE WHEN @ShowPrice = 'M'
THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
+ ( ISNULL(@Margin,
0) / 100.0 ) ))
ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
END AS std_price ,
cs.Main_Label ,
SUM(cs.AvailQOH) AS AvailQOH ,
SUM(cs.QOH) AS QOH ,
CASE WHEN ( EXISTS ( SELECT *
FROM zzxrangd AS Rg
WHERE ( Rg.style = cs.STYLE )
AND ( Rg.color_code = cs.COLOR_CODE )
AND ( Rg.lbl_code = cs.LBL_CODE ) ) )
THEN ( 'Y' )
ELSE ( 'N' )
END AS RangeComponent ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) AS ETADate ,
cs.Roll_Lbl ,
cs.NeckLabel
FROM ( SELECT a_price ,
SUM(AvailQOH) AS AvailQOH ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
SUM(OTSOpen) AS OTSOpen ,
SUM(OTSPlan) AS OTSPlan ,
SUM(QOH) AS QOH ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation ,
MAX(Roll_Lbl) AS Roll_Lbl
FROM [KLL Cut and Sold OTS Sum - New Rollup]
WHERE ( EndLocation IN ( @Location ) )
GROUP BY a_price ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation
) AS cs
INNER JOIN zzxstylr ON cs.DIVISION = zzxstylr.division
AND cs.STYLE = zzxstylr.style
LEFT OUTER JOIN zzxexfvr ON zzxstylr.pkey = zzxexfvr.FKEY
AND zzxexfvr.CONTEXTID = 'ZZXSTYLR'
AND zzxexfvr.prime_image = 'Y'
WHERE cs.DIVISION = 'KLL'
AND ( @MaxQty = 0
OR @MinQty = 0
)
AND ( @MaximumPrice = 0
OR ( @ShowPrice = 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), ( cs.std_cost )
* ( 1 + ( ISNULL(@Margin, 0) / 100.0 ) ))
)
OR ( @ShowPrice <> 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price)
)
)
GROUP BY LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) ,
ISNULL(zzxexfvr.stor_file, ' ') ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
cs.std_cost ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) ,
cs.Size_Ind ,
CONVERT(DECIMAL(10, 2), cs.a_price) ,
cs.Main_Label ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) ,
cs.Roll_Lbl ,
cs.NeckLabel
HAVING ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) > @MinQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
OR ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) BETWEEN 1
AND @MaxQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
ORDER BY cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
cs.Size_Ind ,
cs.EndLocation;
Correct. Now compare with your prior comment:
WHERE ( cs.DIVISION = 'KLL' )
AND ( @MaxQty = 0 )
AND ( @MaximumPrice = 0
OR ( @ShowPrice = 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), ( cs.std_cost )
* ( 1 + ( ISNULL(@Margin, 0) / 100.0 ) ))
)
OR ( @ShowPrice <> 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price)
)
)
OR ( cs.DIVISION = 'KLL' )
AND ( @MinQty = 0 )
AND ( @MaximumPrice = 0
OR ( @ShowPrice = 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), ( cs.std_cost )
* ( 1 + ( ISNULL(@Margin, 0) / 100.0 ) ))
)
OR ( @ShowPrice <> 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price)
)
)
I see the difference in the where however neither query is filtering records. I have the @MaximumPrice set as a float in SSRS. If I type in a value of 5.00 I wouldn't expect to see items with a price above that. However I do.
Please provide some sample data.
Will be good to have a base data, expected results and what are you getting now.
Will be good to have a base data, expected results and what are you getting now.
Here is what I executed. Sample data attached.
DECLARE @Season CHAR(5);
DECLARE @ShowPrice CHAR(1);
DECLARE @Margin DECIMAL(10, 2);
DECLARE @MaximumPrice DECIMAL(10, 2);
DECLARE @MaxQty INT;
DECLARE @MinQty INT;
SET @Season = '20171';
SET @ShowPrice = 'M';
SET @Margin = '15';
SET @MaximumPrice = '6.00';
SET @MaxQty = '0';
SET @MinQty = '0';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DISTINCT
LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) AS Scale_Style ,
ISNULL(zzxexfvr.stor_file, ' ') AS Img_File_Name ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
--SUM(CONVERT(INT, CASE @OTSOVR
-- WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
-- ELSE ( cs.OTSOpen )
-- END)) AS OTS ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) AS std_cost ,
cs.Size_Ind ,
CASE WHEN @ShowPrice = 'M'
THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
+ ( ISNULL(@Margin,
0) / 100.0 ) ))
ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
END AS std_price ,
cs.Main_Label ,
SUM(cs.AvailQOH) AS AvailQOH ,
SUM(cs.QOH) AS QOH ,
CASE WHEN ( EXISTS ( SELECT *
FROM zzxrangd AS Rg
WHERE ( Rg.style = cs.STYLE )
AND ( Rg.color_code = cs.COLOR_CODE )
AND ( Rg.lbl_code = cs.LBL_CODE ) ) )
THEN ( 'Y' )
ELSE ( 'N' )
END AS RangeComponent ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) AS ETADate ,
cs.Roll_Lbl ,
cs.NeckLabel
FROM ( SELECT a_price ,
SUM(AvailQOH) AS AvailQOH ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
SUM(OTSOpen) AS OTSOpen ,
SUM(OTSPlan) AS OTSPlan ,
SUM(QOH) AS QOH ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation ,
MAX(Roll_Lbl) AS Roll_Lbl
FROM [KLL Cut and Sold OTS Sum - New Rollup]
--WHERE ( EndLocation IN ( @Location ) )
GROUP BY a_price ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
STYLE ,
EndLocation
) AS cs
INNER JOIN zzxstylr ON cs.DIVISION = zzxstylr.division
AND cs.STYLE = zzxstylr.style
LEFT OUTER JOIN zzxexfvr ON zzxstylr.pkey = zzxexfvr.FKEY
AND zzxexfvr.CONTEXTID = 'ZZXSTYLR'
AND zzxexfvr.prime_image = 'Y'
WHERE cs.DIVISION = 'KLL'
AND ( @MaxQty = 0
OR @MinQty = 0
)
AND ( @MaximumPrice = 0
OR ( @ShowPrice = 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), ( cs.std_cost )
* ( 1 + ( ISNULL(@Margin, 0) / 100.0 ) ))
)
OR ( @ShowPrice <> 'M'
AND @MaximumPrice <= CONVERT(DECIMAL(10, 2), cs.a_price)
)
)
GROUP BY LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) ,
ISNULL(zzxexfvr.stor_file, ' ') ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
cs.std_cost ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) ,
cs.Size_Ind ,
CONVERT(DECIMAL(10, 2), cs.a_price) ,
cs.Main_Label ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) ,
cs.Roll_Lbl ,
cs.NeckLabel
HAVING ( cs.season IN ( @Season ) )
-- AND ( cs.group_code4 IN ( @Size_Scale ) )
-- AND ( cs.LBL_CODE IN ( @Label ) )
-- AND ( cs.Main_Label IN ( @Main_Label ) )
-- AND ( SUM(CONVERT(INT, CASE @OTSOVR
-- WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
-- ELSE ( cs.OTSOpen )
-- END)) > @MinQty )
-- AND ( cs.Classification IN ( @Class ) )
-- AND ( cs.NeckLabel IN ( @NeckLabels ) )
-- OR ( cs.season IN ( @Season ) )
-- AND ( cs.group_code4 IN ( @Size_Scale ) )
-- AND ( cs.LBL_CODE IN ( @Label ) )
-- AND ( cs.Main_Label IN ( @Main_Label ) )
-- AND ( SUM(CONVERT(INT, CASE @OTSOVR
-- WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
-- ELSE ( cs.OTSOpen )
-- END)) BETWEEN 1
-- AND @MaxQty )
-- AND ( cs.Classification IN ( @Class ) )
-- AND ( cs.NeckLabel IN ( @NeckLabels ) )
ORDER BY cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
cs.Size_Ind ,
cs.EndLocation;
Sorry but that doesn't help.
It only shows what's returned but doesn't show what SHOULD be returned, so we can see what's wrong and what's missing.
And sample from raw data will be also helpful to understand the logic needed.
It only shows what's returned but doesn't show what SHOULD be returned, so we can see what's wrong and what's missing.
And sample from raw data will be also helpful to understand the logic needed.
I would think that the data clearly shows that the @MaximumPrice value is set to $6.00 however the query is returning records with a value greater than $6.00.
Let me put it this way:
- I'm making a cake and don't want it to have sugar. I gave you the cake to try and you tell me that the cake has sugar and so you asked me for the ingredients to see where the sugar is coming from and then I give you whole cake for you to check where the sugar is coming from.
Giving you the cake would help you find where the sugar is coming from?
Or it just confirms that the cake has sugar? Something that we already know.
- I'm making a cake and don't want it to have sugar. I gave you the cake to try and you tell me that the cake has sugar and so you asked me for the ingredients to see where the sugar is coming from and then I give you whole cake for you to check where the sugar is coming from.
Giving you the cake would help you find where the sugar is coming from?
Or it just confirms that the cake has sugar? Something that we already know.
Attached is a sample of raw data. Interestingly enough I set the @MaximumValue to $1.00 and I get more records.
SampleRaw.xlsx
SampleRaw.xlsx
I guess my first question is should I just be passing a null for @MaximumPrice if a user hasn't entered anything in the parameter? Does this make a difference versus passing a default of 0? I know that you mentioned that this should be in the Where however I thought I could use alias in the Having?
I'm also using SQL2012 so in theory could I do something like this? Obviously my situation is a bit more involved.
Or IIF() function if you’re using SQL Server 2012:
SELECT ProductID, ProductName,ProductDesc
FROM product
WHERE ProductID =IIF(@productID IS NULL, ProductID, @productID )
I'm also using SQL2012 so in theory could I do something like this? Obviously my situation is a bit more involved.
Or IIF() function if you’re using SQL Server 2012:
SELECT ProductID, ProductName,ProductDesc
FROM product
WHERE ProductID =IIF(@productID IS NULL, ProductID, @productID )
You definitely need to move you HAVING clause to the WHERE clause. Add the following to your WHERE clause:
Also, you should stick with the correct data types. You created numeric variables and then setting them as strings. Don't do that, even they can be implicit converted but in case of error it will be hard to troubleshoot (for example a typo):
Will check the rest.
WHERE cs.season = @Season
AND ...
Also, you should stick with the correct data types. You created numeric variables and then setting them as strings. Don't do that, even they can be implicit converted but in case of error it will be hard to troubleshoot (for example a typo):
SET @Margin = 15;
SET @MaximumPrice = 6.00;
SET @MaxQty = 0;
SET @MinQty = 0;
Will check the rest.
I'm also using SQL2012 so in theory could I do something like this? Obviously my situation is a bit more involved.Yes, this can be done.
Or IIF() function if you’re using SQL Server 2012:
SELECT ProductID, ProductName,ProductDesc
FROM product
WHERE ProductID =IIF(@productID IS NULL, ProductID, @productID )
I solved the issue. I set the alias on the Inner Select to simplify the code in my WHERE. For my WHERE I used the following
Worked like a charm
)
AND ( cs.std_price < = @MaximumPrice
OR ISNULL(@MaximumPrice, '0') = '0'
)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DISTINCT
LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) AS Scale_Style ,
ISNULL(zzxexfvr.stor_file, ' ') AS Img_File_Name ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) AS OTS ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
SUM(CONVERT(DECIMAL(10, 2), cs.std_cost)) AS std_cost ,
cs.Size_Ind ,
SUM(CONVERT(DECIMAL(10, 2), cs.std_price)) AS std_price ,
cs.Main_Label ,
SUM(cs.AvailQOH) AS AvailQOH ,
SUM(cs.QOH) AS QOH ,
CASE WHEN ( EXISTS ( SELECT *
FROM zzxrangd AS Rg
WHERE ( Rg.style = cs.STYLE )
AND ( Rg.color_code = cs.COLOR_CODE )
AND ( Rg.lbl_code = cs.LBL_CODE ) ) )
THEN ( 'Y' )
ELSE ( 'N' )
END AS RangeComponent ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) AS ETADate ,
cs.Roll_Lbl ,
cs.NeckLabel
FROM ( SELECT a_price ,
SUM(AvailQOH) AS AvailQOH ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
SUM(OTSOpen) AS OTSOpen ,
SUM(OTSPlan) AS OTSPlan ,
SUM(QOH) AS QOH ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
CASE WHEN @ShowPrice = 'M'
THEN ( std_cost ) * ( 1 + ( ISNULL(@Margin, 0)
/ 100.0 ) )
ELSE a_price
END AS std_price ,
STYLE ,
EndLocation ,
MAX(Roll_Lbl) AS Roll_Lbl
FROM [KLL Cut and Sold OTS Sum - New Rollup]
WHERE ( EndLocation IN ( @Location ) )
GROUP BY a_price ,
Classification ,
COLOR_CODE ,
DIVISION ,
group_code4 ,
LBL_CODE ,
Main_Label ,
NeckLabel ,
Scale_Name ,
season ,
SeaSyrScale ,
Size_Ind ,
std_cost ,
CASE WHEN @ShowPrice = 'M'
THEN ( std_cost ) * ( 1 + ( ISNULL(@Margin, 0)
/ 100.0 ) )
ELSE a_price
END ,
STYLE ,
EndLocation
) AS cs
INNER JOIN zzxstylr ON cs.DIVISION = zzxstylr.division
AND cs.STYLE = zzxstylr.style
LEFT OUTER JOIN zzxexfvr ON zzxstylr.pkey = zzxexfvr.FKEY
AND zzxexfvr.CONTEXTID = 'ZZXSTYLR'
AND zzxexfvr.prime_image = 'Y'
WHERE cs.DIVISION = 'KLL'
AND ( @MaxQty = 0
OR @MinQty = 0
)
AND ( cs.std_price < = @MaximumPrice
OR ISNULL(@MaximumPrice, '0') = '0'
)
GROUP BY LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) ,
ISNULL(zzxexfvr.stor_file, ' ') ,
cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.EndLocation ,
cs.std_cost ,
cs.std_price ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
CONVERT(DECIMAL(10, 2), cs.std_cost) ,
cs.Size_Ind ,
CONVERT(DECIMAL(10, 2), cs.a_price) ,
cs.Main_Label ,
cs.season ,
cs.Classification ,
dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
cs.COLOR_CODE,
cs.LBL_CODE,
cs.group_code4) ,
cs.Roll_Lbl ,
cs.NeckLabel
HAVING ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) > @MinQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
OR ( cs.season IN ( @Season ) )
AND ( cs.group_code4 IN ( @Size_Scale ) )
AND ( cs.LBL_CODE IN ( @Label ) )
AND ( cs.Main_Label IN ( @Main_Label ) )
AND ( SUM(CONVERT(INT, CASE @OTSOVR
WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
ELSE ( cs.OTSOpen )
END)) BETWEEN 1
AND @MaxQty )
AND ( cs.Classification IN ( @Class ) )
AND ( cs.NeckLabel IN ( @NeckLabels ) )
ORDER BY cs.Scale_Name ,
cs.group_code4 ,
cs.SeaSyrScale ,
cs.STYLE ,
cs.COLOR_CODE ,
cs.LBL_CODE ,
cs.Size_Ind ,
cs.EndLocation;
Worked like a charm
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions