Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.
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;
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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'.
You can't use an alias from your SELECT statement in the correspondent WHERE clause.
I can see a lot of mistakes in your query. For example, in your WHERE clause you're not using the parenthesis properly. Be very careful when working with the OR operator. The correct should be:
You souldn't need DISTINCT clause when using GROUP BY so review this part also.
I can see a lot of mistakes in your query. For example, in your WHERE clause you're not using the parenthesis properly. Be very careful when working with the OR operator. The correct should be:
WHERE cs.DIVISION = 'KLL' AND (@MaxQty = 0 OR @MinQty = 0)
You souldn't need DISTINCT clause when using GROUP BY so review this part also.
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
So, you just changed the comparation.
In your main question your request was:
but you ended up by swapping it:
Glad that you find it by your own.
You may close this question now.
Cheers
In your main question your request was:
WHERE ( cs.DIVISION = 'KLL' )
AND @MaximumPrice = 0
OR @MaximumPrice <= cs.a_price
OR @MaximumPrice <= cs.std_cost
AND @MaximumPrice = 0
OR @MaximumPrice <= cs.a_price
OR @MaximumPrice <= cs.std_cost
but you ended up by swapping it:
cs.std_price < = @MaximumPrice
Glad that you find it by your own.
You may close this question now.
Cheers
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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
)