Avatar of Zack
Zack
Flag for Australia asked on

SQL - Undeclared scalar table variable

Hi EE,

I am having issues with this table variable 

declare @DifferenceTable  table (
        DataAreaId nvarchar(10) ,
        SalesId nvarchar(50) ,
        InvoiceId nvarchar(50) ,
        ItemId nvarchar(50),
        InvoiceQty decimal(16,4) ,
        InventoryQty decimal(16,4)
    )




insert into @DifferenceTable (DataAreaId , SalesId , InvoiceId , ItemId , InvoiceQty ,  InventoryQty )
    select CustInvoiceTrans.DATAAREAID , CustInvoiceTrans.SALESID , CustInvoiceTrans.INVOICEID , CustInvoiceTrans.ITEMID , sum(CustInvoiceTrans.QTY) , 0. as InventoryQty
    from CustInvoiceTrans with(nolock)    
    group by CustInvoiceTrans.DATAAREAID , CustInvoiceTrans.SALESID , CustInvoiceTrans.INVOICEID , CustInvoiceTrans.ITEMID


update @DifferenceTable
    set InventoryQty =  tt.Qty from
                                           (select sum(InventTrans.QTY)*-1 as Qty , InventTransOrigin.DATAAREAID , REFERENCEID , InventTrans.INVOICEID , InventTrans.ITEMID
                                            from InventTransOrigin
                                             left join InventTrans      on InventTrans.INVENTTRANSORIGIN = InventTransOrigin.RECID
                                                                                    and InventTrans.DATAAREAID = InventTransOrigin.DATAAREAID
                                                                                    and InventTrans.ITEMID = InventTransOrigin.ITEMID
                                             where REFERENCECATEGORY = 0
                                             group by InventTransOrigin.DATAAREAID , REFERENCEID , InventTrans.INVOICEID , InventTrans.ITEMID
                                            ) tt
                                    where  @DifferenceTable.DataAreaId =  tt.DATAAREAID and @DifferenceTable.SalesId = tt.REFERENCEID
                                    and tt.INVOICEID = @DifferenceTable.InvoiceId and tt.ITEMID = @DifferenceTable.ItemId  


select * from @DifferenceTable

Open in new window

The @differencetable isn't recognised as declared, how would I resolve this?

Any assistance is welcome. 

Thank you

SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Dustin Saunders

8/22/2022 - Mon
Ryan Chong

quick try...

update @DifferenceTable
    set InventoryQty =  tt.Qty from
                                           (select sum(InventTrans.QTY)*-1 as Qty , InventTransOrigin.DATAAREAID , REFERENCEID , InventTrans.INVOICEID , InventTrans.ITEMID
                                            from InventTransOrigin
                                             left join InventTrans      on InventTrans.INVENTTRANSORIGIN = InventTransOrigin.RECID
                                                                                    and InventTrans.DATAAREAID = InventTransOrigin.DATAAREAID
                                                                                    and InventTrans.ITEMID = InventTransOrigin.ITEMID
                                             where REFERENCECATEGORY = 0
                                             group by InventTransOrigin.DATAAREAID , REFERENCEID , InventTrans.INVOICEID , InventTrans.ITEMID
                                            ) tt inner join @DifferenceTable
                                    on  @DifferenceTable.DataAreaId =  tt.DATAAREAID and @DifferenceTable.SalesId = tt.REFERENCEID
                                    and tt.INVOICEID = @DifferenceTable.InvoiceId and tt.ITEMID = @DifferenceTable.ItemId

Open in new window


SQL Server UPDATE JOIN

https://www.sqlservertutorial.net/sql-server-basics/sql-server-update-join/

Zack

ASKER
Hi Ryan

No unfortunately same errors :(

Thank you. 
ASKER CERTIFIED SOLUTION
Dustin Saunders

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dustin Saunders

Btw, not sure what size of data you're working with but there are performance differences between Temp Tables and Variable Tables, especially when doing UPDATE operations, so you might want to consider that as well.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Zack

ASKER
Hi Dustin/Ryan,

Many thanks for your assistance.

Dustin on your performance comment: Good point but I'm dealing with around 60000-80000 rows of data so not a severe performance issue. 
Dustin Saunders

Sounds good, and happy to help!