troubleshooting Question

Syntax issue with my Where Clause SQL 2012

Avatar of mburk1968
mburk1968Flag for United States of America asked on
Microsoft SQL ServerSQL
20 Comments2 Solutions124 ViewsLast Modified:
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

My issue is when I try to use std_price in my where. I receive an error saying that it is an invalid column name.  So then I tried doing something like this.
WHERE   ( cs.DIVISION = 'KLL' )
        AND @MaximumPrice = 0
        OR @MaximumPrice <= cs.a_price
        OR @MaximumPrice <= cs.std_cost

However that doesn't work the way I expected either. Basically I need my where to respond as so. If @MaximumPrice is 0 show me everything. If @MaximumPrice has a value "This is a user entered Parm" then only show me records that have a std_price that is < = to the @MaximumPrice.

He is my entire query

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT DISTINCT
        LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) AS Scale_Style ,
        ISNULL(zzxexfvr.stor_file, ' ') AS Img_File_Name ,
        cs.Scale_Name ,
        cs.group_code4 ,
        cs.SeaSyrScale ,
        cs.STYLE ,
        cs.EndLocation ,
        SUM(CONVERT(INT, CASE @OTSOVR
                           WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
                           ELSE ( cs.OTSOpen )
                         END)) AS OTS ,
        cs.COLOR_CODE ,
        cs.LBL_CODE ,
        CONVERT(DECIMAL(10, 2), cs.std_cost) AS std_cost ,
        cs.Size_Ind ,
        CASE WHEN @ShowPrice = 'M'
             THEN CONVERT(DECIMAL(10, 2), ( cs.std_cost ) * ( 1
                                                              + ( ISNULL(@Margin,
                                                              0) / 100.0 ) ))
             ELSE CONVERT(DECIMAL(10, 2), cs.a_price)
        END AS std_price ,
        cs.Main_Label ,
        SUM(cs.AvailQOH) AS AvailQOH ,
        SUM(cs.QOH) AS QOH ,
        CASE WHEN ( EXISTS ( SELECT *
                             FROM   zzxrangd AS Rg
                             WHERE  ( Rg.style = cs.STYLE )
                                    AND ( Rg.color_code = cs.COLOR_CODE )
                                    AND ( Rg.lbl_code = cs.LBL_CODE ) ) )
             THEN ( 'Y' )
             ELSE ( 'N' )
        END AS RangeComponent ,
        cs.season ,
        cs.Classification ,
        dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
                                                            cs.COLOR_CODE,
                                                            cs.LBL_CODE,
                                                            cs.group_code4) AS ETADate ,
        cs.Roll_Lbl ,
        cs.NeckLabel
FROM    ( SELECT    a_price ,
                    SUM(AvailQOH) AS AvailQOH ,
                    Classification ,
                    COLOR_CODE ,
                    DIVISION ,
                    group_code4 ,
                    LBL_CODE ,
                    Main_Label ,
                    NeckLabel ,
                    SUM(OTSOpen) AS OTSOpen ,
                    SUM(OTSPlan) AS OTSPlan ,
                    SUM(QOH) AS QOH ,
                    Scale_Name ,
                    season ,
                    SeaSyrScale ,
                    Size_Ind ,
                    std_cost ,
                    STYLE ,
                    EndLocation ,
                    MAX(Roll_Lbl) AS Roll_Lbl
          FROM      [KLL Cut and Sold OTS Sum - New Rollup]
          WHERE     ( EndLocation IN ( @Location ) )
          GROUP BY  a_price ,
                    Classification ,
                    COLOR_CODE ,
                    DIVISION ,
                    group_code4 ,
                    LBL_CODE ,
                    Main_Label ,
                    NeckLabel ,
                    Scale_Name ,
                    season ,
                    SeaSyrScale ,
                    Size_Ind ,
                    std_cost ,
                    STYLE ,
                    EndLocation
        ) AS cs
        INNER JOIN zzxstylr ON cs.DIVISION = zzxstylr.division
                               AND cs.STYLE = zzxstylr.style
        LEFT OUTER JOIN zzxexfvr ON zzxstylr.pkey = zzxexfvr.FKEY
                                    AND zzxexfvr.CONTEXTID = 'ZZXSTYLR'
                                    AND zzxexfvr.prime_image = 'Y'
WHERE   ( cs.DIVISION = 'KLL' )
        AND ( @MaxQty = 0 )
        OR ( cs.DIVISION = 'KLL' )
        AND ( @MinQty = 0 )
GROUP BY LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) ,
        ISNULL(zzxexfvr.stor_file, ' ') ,
        cs.Scale_Name ,
        cs.group_code4 ,
        cs.SeaSyrScale ,
        cs.STYLE ,
        cs.EndLocation ,
        cs.std_cost ,
        cs.COLOR_CODE ,
        cs.LBL_CODE ,
        CONVERT(DECIMAL(10, 2), cs.std_cost) ,
        cs.Size_Ind ,
        CONVERT(DECIMAL(10, 2), cs.a_price) ,
        cs.Main_Label ,
        cs.season ,
        cs.Classification ,
        dbo.KLLfn_Get_Max_ETAWhs_Date_Style_Color_Lbl_Scale(cs.STYLE,
                                                            cs.COLOR_CODE,
                                                            cs.LBL_CODE,
                                                            cs.group_code4) ,
        cs.Roll_Lbl ,
        cs.NeckLabel
HAVING  ( cs.season IN ( @Season ) )
        AND ( cs.group_code4 IN ( @Size_Scale ) )
        AND ( cs.LBL_CODE IN ( @Label ) )
        AND ( cs.Main_Label IN ( @Main_Label ) )
        AND ( SUM(CONVERT(INT, CASE @OTSOVR
                                 WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
                                 ELSE ( cs.OTSOpen )
                               END)) > @MinQty )
        AND ( cs.Classification IN ( @Class ) )
        AND ( cs.NeckLabel IN ( @NeckLabels ) )
        OR ( cs.season IN ( @Season ) )
        AND ( cs.group_code4 IN ( @Size_Scale ) )
        AND ( cs.LBL_CODE IN ( @Label ) )
        AND ( cs.Main_Label IN ( @Main_Label ) )
        AND ( SUM(CONVERT(INT, CASE @OTSOVR
                                 WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
                                 ELSE ( cs.OTSOpen )
                               END)) BETWEEN 1
                                     AND     @MaxQty )
        AND ( cs.Classification IN ( @Class ) )
        AND ( cs.NeckLabel IN ( @NeckLabels ) )
ORDER BY cs.Scale_Name ,
        cs.group_code4 ,
        cs.SeaSyrScale ,
        cs.STYLE ,
        cs.COLOR_CODE ,
        cs.LBL_CODE ,
        cs.Size_Ind ,
        cs.EndLocation;
ASKER CERTIFIED SOLUTION
Vitor Montalvão
IT Engineer
Join our community to see this answer!
Unlock 2 Answers and 20 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 20 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros