Post reply

Dataset creating a second Parameter, instead of just one

  • April 12, 2012 at 12:27 pm

    #267314

    For some reason (using Visual Studio) my query is creating two parameters for @model, when you run it, when it should just be re-using it. Is there some way I can get it to work like it does in SQL Server Management Studio ? My guess is that it is trying to treat the feeding SQL statement's '@model' as different than the second query's.

    WITH basesql AS (

    select A.ModelCode

    , A.MaterialCode

    , B.PlantCode

    , B.DIVISION

    , sum( A.SETUPTIMEHOURS ) 'HRS'

    , count(*) 'Total_Count'

    , sum( case when A.FZFLAG = '1' OR A.FIXEDLOTNUMBER <> 'X'

    then 1 else 0 end ) 'Count_Frozen'

    , sum( case when A.FZFLAG = '0' AND A.FIXEDLOTNUMBER = 'X'

    then 1 else 0 end ) 'Count_Unfrozen'

    , sum( case when A.FZFLAG = '1' OR A.FIXEDLOTNUMBER <> 'X'

    then A.SETUPTIMEHOURS else 0 end ) 'HRS_Frozen'

    , sum( case when A.FZFLAG = '0' AND A.FIXEDLOTNUMBER = 'X'

    then A.SETUPTIMEHOURS else 0 end ) 'HRS_Unfrozen'

    from MOM_GSCS_SETUP_CLEANING_DETAIL A

    join MOM_GSCS_MODELS B

    on A.Modelcode = B.Modelcode

    WHERE @model = 'all' and @plant = 'all' and @div = 'all'

    or @div = 'all' and @plant = 'all' and @model = A.ModelCode

    or @div = 'all' and @plant = B.plantcode and @model = 'all'

    or @div = 'all' and @plant = B.plantcode and @model = A.ModelCode

    or @div = B.division and @plant = 'all' and @model = 'all'

    or @div = B.division and @plant = 'all' and @model = A.ModelCode

    or @div = B.division and @plant = B.plantcode and @model = 'all'

    or @div = B.division and @plant = B.plantcode and @model = A.ModelCode

    group by B.division

    , B.PlantCode

    , A.ModelCode

    , A.MaterialCode

    )

    SELECT TOP 20 MODELCODE, MATERIALCODE, HRS, TOTAL_COUNT, COUNT_FROZEN, COUNT_UNFROZEN, HRS_FROZEN, HRS_UNFROZEN

    FROM basesql

    WHERE MODELCODE = @Model

    ORDER BY HRS DESC

  • April 12, 2012 at 11:50 pm

    #1473042

    Case sensitivity. Name them both @model.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • April 13, 2012 at 7:48 am

    #1473250

    Interesting.. thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply