Must declare the scalar variable "@shippingDateConfirmed" message 1

[spiego]

In SSRS 2005 when I execute the query shown, I get the message shown below. Any ideas on why it is complaining?

   declare @rptset Table(
      rSalesName        nvarchar(100),
      rCustAccount      nvarchar(10),
      rInvoiceAccount   nvarchar(10),
      rStoreName        nvarchar(100),
      ritemid                 nvarchar(20),
      ritemName         nvarchar(60),
      rordered          numeric(28,12),
      rshipped          numeric(28,12),
      rlocked                 tinyint,
      rsalesId          nvarchar(20)
)


insert into @rptset(rsalesName,rcustAccount,rinvoiceaccount,rstorename,ritemid,ritemname,rordered,rshipped,rlocked,rsalesid)
select
salestable.salesname,
salestable.custaccount,
case when salestable.invoiceaccount is null or salestable.invoiceaccount = '' then salestable.custaccount
else salestable.invoiceaccount
end as derivedinvoiceaccount,
salestable.asistorename,
salesline.itemid,
salesline.ASIITEMNAME,
sum(qtyordered) as ordered,
isnull(shippedqty,0.0) as shippedqty,
salesLine.lockpricing,
salestable.salesid
from
salestable join salesline on salestable.salesid = salesline.salesid and salestable.dataareaid = salesline.dataareaid
left outer join
(select 
itemid,CustPackingSlipJour.salesid,CustPackingSlipJour.dataareaid,sum(inventqty) shippedqty
 from 
CustPackingSlipJour join CustPackingSlipTrans 
on CustPackingSlipJour.packingslipid=CustPackingSlipTrans.packingslipid and
CustPackingSlipJour.dataareaid=CustPackingSlipTrans.dataareaid
group by itemid,CustPackingSlipJour.dataareaid,CustPackingSlipJour.salesid) x 
on salesline.itemid = x.itemid and salesline.dataareaid = x.dataareaid and salesline.salesid = x.salesid

where 
--salestable.salesid = @salesid and
salestable.salesstatus in (2,3) and
salestable.shippingdateconfirmed = @shippingDateConfirmed and
salestable.dataareaid = 'nuc'
group by 
salestable.salesname,
salestable.custaccount,
case when salestable.invoiceaccount is null or salestable.invoiceaccount = '' then salestable.custaccount
else salestable.invoiceaccount
end,
salestable.asistorename,
salesline.itemid,
salesline.ASIITEMNAME,
x.shippedqty,
salesLine.lockpricing,
salestable.salesid

declare @origItem nvarchar(20), @subItem nvarchar(20)
declare @convertFactor numeric(28,12)
declare @shippedSub           numeric(28,12)

declare subCursor cursor
for
select ritemid,rordered,rshipped,rsalesid
from @rptset
where rlocked=1
declare @ritemid nvarchar(20), @rordered numeric(28,12), @rshipped numeric(28,12), @rsalesId nvarchar(20)
open subCursor
fetch next from subCursor into @ritemid,@rordered,@rshipped,@rsalesId
while(@@Fetch_status<>-1)
begin
      select top 1 @origItem = OriginalItemId from
      asiitemsubstitutes join @rptset on 
      asiitemsubstitutes.OriginalItemId = ritemid and
      rsalesid = @rsalesId
      -- add soid here
      where
      rlocked = 0 and
      substituteitemid = @ritemid
      
      if @origItem is not null and len(@origItem)>0 
      begin
            update @rptset set rordered = rordered - @rshipped
            where ritemid=@origItem and rsalesid = @rsalesid
      end
      
      fetch next from subCursor into @ritemid,@rordered,@rshipped,@rsalesId
end
close subCursor
deallocate subCursor

if @origItem is not null and @subItem is not null and len(@origItem)>0 and len(@subItem)>0 
begin
      select @shippedSub = rshipped from @rptset where ritemid = @subItem
      update @rptset set rordered=rordered - (@shippedSub * @convertFactor) where ritemid = @origItem
end

select * from @rptset
where
rordered!=rshipped

Thanks - Dean

 

[xlbo]

without wishing to state the obvious, it is because you are using a variable: @shippingDateConfirmed

and you have not declared it anywhere

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[spiego]

Sometimes we need the obvious pointed out to us. It no longer gives that error, however, when I first open up this report and go to the data tab, a message appears saying "The Declare cursor SQL construct or statement is not supported." I click on OK and I am able to run the sql. Any thoughts on this? This is my first time using a cursor in SSRS 2005.

Thanks - Dean

 

[xlbo]

Sorry - not used cursors for report SQL.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question