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.

        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

Open in new window


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

Open in new window


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;

Open in new window

mburk1968Asked:
Who is Participating?
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.

aranaCommented:
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
)
0
mburk1968Author Commented:
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'.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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:
WHERE cs.DIVISION = 'KLL' AND (@MaxQty = 0 OR @MinQty = 0)

Open in new window

Also, in the HAVING clause you should use only filters for the functions (SUM, COUNT, AVG, MIN, MAX) and not for other fields. Those ones should be kept in the WHERE clause. And it's useless to perform an IN operation with variables.
You souldn't need DISTINCT clause when using GROUP BY so review this part also.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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))
		)

Open in new window

0
mburk1968Author Commented:
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;

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I've applied code to my where clause
No, 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.
0
mburk1968Author Commented:
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;

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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)
                 )
            )

Open in new window

Can you see the difference?
0
mburk1968Author Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please provide some sample data.
Will be good to have a base data, expected results and what are you getting now.
0
mburk1968Author Commented:
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;

Open in new window

Sample-Data.xlsx
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
mburk1968Author Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
mburk1968Author Commented:
Attached is a sample of raw data. Interestingly enough I set the @MaximumValue to $1.00 and I get more records.
SampleRaw.xlsx
0
mburk1968Author Commented:
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 )
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You definitely need to move you HAVING clause to the WHERE clause. Add the following to your WHERE clause:
WHERE cs.season = @Season 
    AND ...

Open in new window

I didn't have much time to see your raw data properly but I didn't find any Season = '20171'.

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;

Open in new window


Will check the rest.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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 )
Yes, this can be done.
0
mburk1968Author Commented:
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

            )
        AND ( cs.std_price < = @MaximumPrice
              OR ISNULL(@MaximumPrice, '0') = '0'
            )

Open in new window



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;

Open in new window


Worked like a charm
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, you just changed the comparation.
In your main question your request was:
WHERE   ( cs.DIVISION = 'KLL' )
        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
0

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 trial
It'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.