We help IT Professionals succeed at work.

Syntax issue with my Where Clause SQL 2012

117 Views
Last Modified: 2017-04-06
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

Comment
Watch Question

CERTIFIED EXPERT

Commented:
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
)

Author

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'.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Author

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

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

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

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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?

Author

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.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Please provide some sample data.
Will be good to have a base data, expected results and what are you getting now.

Author

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
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

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.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

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

Author

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 )
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

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
IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions