Declare issues in MS SQL SERVER Report Builder

mklgxw1f 于 8个月前发布在 SQL Server

关注(0)|答案(1)|浏览(88)

I am having issues with an error message constantly occurring, which states "Must declare scalar variable "@station".

Also, I am trying to set "specific" stations into "units" via Boolean but I don't know if that is the correct way to use the parameter "@station"

If someone could point me in the right direction I would seriously appreciate it.

declare @Dbgn datetime,
    @Dend datetime;
set @Dbgn = @dateFrom + @ShiftStart;
set @Dend = @Dbgn + 1;

if @station = 1
begin
  -- set (Fields!unit.Value =(695, 696, 697 ,698, 651, 652, 653, 654, 655, 656, 657));
end
else
begin
  -- set (Fields!unit.Value =(51, 52, 221, 531, 532, 533, 534));
end
begin
SELECT  operator,
    unit,
    case
         when isNull(ScriptRate,0) > 0 and isNull(ItemRate,0) = 0 then 'Scripts'
         when isNull(ScriptRate,0) = 0 and isNull(ItemRate,0) > 0 then 'Items'
         when isNull(ScriptRate,0) > 0 and isNull(ItemRate,0) > 0 then '-X-'
         else '?'
    end  AS Type,
    SUM(ISNULL(numorders, 0))    AS totalorders,
    SUM(ISNULL(numscripts, 0))   AS totalscripts,
    SUM(ISNULL(numitems, 0))     AS totalitems,
    SUM(ISNULL(minuteslogon, 0)) AS totalminutes,
    case 
         when activityhour = 0 then 24
         else activityhour
    end as activityhour,
    sum( case
         when isNull(ScriptRate,0) > 0 and isNull(ItemRate,0) = 0 then ss.numscripts
         when isNull(ScriptRate,0) = 0 and isNull(ItemRate,0) > 0 then ss.NUMITEMS
         else 0
    end )     AS totalpvitems,
    avg( case
         when isNull(ScriptRate,0) > 0 and isNull(ItemRate,0) = 0 then unit.ScriptRate
         when isNull(ScriptRate,0) = 0 and isNull(ItemRate,0) > 0 then unit.ItemRate
         else 0
    end )     AS targetRate
FROM    mck_hvs.stationstats ss with( nolock ),
    mck_hvs.unit with( nolock )
WHERE   ss.unit = unit.unitno
    and activitytype > 1
    and ( createdate >= cast( @Dbgn as datetime) )
    and ( logofftime <= cast( @Dend as datetime) )

GROUP BY operator,
    unit,
    case
         when isNull(ScriptRate,0) > 0 and isNull(ItemRate,0) = 0 then 'Scripts'
         when isNull(ScriptRate,0) = 0 and isNull(ItemRate,0) > 0 then 'Items'
         when isNull(ScriptRate,0) > 0 and isNull(ItemRate,0) > 0 then '-X-'
         else '?'
    end,
    case 
         when activityhour = 0 then 24
         else activityhour
    end
ORDER BY operator asc,
    unit desc,
    activityhour
end

sql-server

来源：https://stackoverflow.com/questions/42984389/declare-issues-in-ms-sql-server-report-builder

1条答案

按热度按时间

6tr1vspr1#

The problem with your code is that your code is that the variable '@station' was not declared but used. You need to declare @station

DECLARE @Dbgn datetime,
    @Dend datetime,
    @station int; -- Declare @station as an integer, assuming it's an 
integer parameter.

-- Set your other variables and parameters
SET @Dbgn = @dateFrom + @ShiftStart;
SET @Dend = @Dbgn + 1;

-- The rest of your code follows
if @station = 1
begin
  -- Your logic for station = 1
end
else
begin
  -- Your logic for station not equal to 1
end

 -- The SELECT statement and the rest of your code follows

赞(0）回复(0）举报 7个月前

我来回答

Declare issues in MS SQL SERVER Report Builder

1条答案

相关问题

热门标签

最新问答