Avatar of mburk1968
mburk1968
Flag for United States of America asked on

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

Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Arana (G.P.)

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
)
mburk1968

ASKER
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'.
SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
mburk1968

ASKER
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

Vitor Montalvão

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.
mburk1968

ASKER
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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

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?
mburk1968

ASKER
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.
Vitor Montalvão

Please provide some sample data.
Will be good to have a base data, expected results and what are you getting now.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
mburk1968

ASKER
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
Vitor Montalvão

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.
mburk1968

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

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.
mburk1968

ASKER
Attached is a sample of raw data. Interestingly enough I set the @MaximumValue to $1.00 and I get more records.
SampleRaw.xlsx
mburk1968

ASKER
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 )
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Vitor Montalvão

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.
Vitor Montalvão

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.
mburk1968

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.