November 26, 2008 at 7:24 am
I get the following error when trying to update data in a table variable:
Server: Msg 137, Level 15, State 2, Procedure x_job_cash_flow, Line 49
Must declare the variable '@x_ap_payment_summary'.
Here is the entire sql statement. Line 49 is the line that has
update @x_job_cash_flow set inv_payment_amt = @x_ap_payment_summary.inv_payment_amt
on it. What am I missing? Thank you in advance.
create proc x_job_cash_flow as
declare @x_job_cash_flow table(company_code varchar(10) NOT NULL ,
job_number varchar(10) NOT NULL ,
vendor_code varchar(10) NOT NULL ,
invoice_number varchar(50) NOT NULL ,
invoice_type_code varchar(1) NOT NULL ,
total_amount decimal(18, 2) NOT NULL ,
invoice_amount decimal(18, 2) NOT NULL ,
invoice_ratio decimal(18, 10) NULL ,
inv_payment_amt decimal(18, 2) NULL ,
job_payment_amt decimal(18, 2) NULL)
declare @x_ap_payment_summary table (company_code varchar(10) NOT NULL ,
vendor_code varchar(10) NOT NULL ,
invoice_number varchar(50) NOT NULL ,
invoice_type_code varchar(1) NOT NULL ,
inv_payment_amt decimal(18, 2) NULL)
insert into @x_job_cash_flow
(company_code, job_number, vendor_code, invoice_number, invoice_type_code, total_amount, invoice_amount)
SELECT DETAIL.COMPANY_CODE, DETAIL.JOB_NUMBER, DETAIL.VENDOR_CODE, DETAIL.INVOICE_NUMBER, DETAIL.INVOICE_TYPE_CODE,
SUM((DETAIL.DEBIT_AMOUNT + DETAIL.CREDIT_AMOUNT + DETAIL.TAX_AMOUNT) *
CASE DETAIL.INVOICE_TYPE_CODE
WHEN 'I' THEN 1
ELSE -1
END) AS TOTAL_AMOUNT,
MAX(HEADER_QUERY.INVOICE_AMOUNT) AS INVOICE_AMOUNT
FROM VN_GL_DISTRIBUTION_DETAIL_MC DETAIL (NOLOCK)
INNER JOIN
(SELECT Company_Code, Vendor_Code, Invoice_Number, Invoice_Type_Code, CASE INVOICE_TYPE_CODE
WHEN 'I' THEN INVOICE_AMOUNT
ELSE INVOICE_AMOUNT * -1
END AS INVOICE_AMOUNT
FROM VN_GL_DISTRIBUTION_HEADER_MC (NOLOCK)) AS HEADER_QUERY
ON DETAIL.COMPANY_CODE = HEADER_QUERY.COMPANY_CODE AND
DETAIL.VENDOR_CODE = HEADER_QUERY.VENDOR_CODE AND
DETAIL.INVOICE_NUMBER = HEADER_QUERY.INVOICE_NUMBER AND
DETAIL.INVOICE_TYPE_CODE = HEADER_QUERY.INVOICE_TYPE_CODE
WHERE DETAIL.COMPANY_CODE = 'WOR' AND DETAIL.COMPANY_CODE <> DETAIL.SEQUENCE
GROUP BY DETAIL.COMPANY_CODE, DETAIL.JOB_NUMBER, DETAIL.VENDOR_CODE, DETAIL.INVOICE_NUMBER, DETAIL.INVOICE_TYPE_CODE
insert into @x_ap_payment_summary
select company_code, vendor_code, invoice_number, invoice_type_code, sum(payment_amount + discount_taken) as inv_payment_amt
from vn_payment_history_mc (nolock)
where company_code = 'WOR'
group by company_code, vendor_code, invoice_number, invoice_type_code
--below is line 49
update @x_job_cash_flow set inv_payment_amt = @x_ap_payment_summary.inv_payment_amt
from @x_ap_payment_summary
where @x_job_cash_flow.company_code = @x_ap_payment_summary.company_code
and @x_job_cash_flow.vendor_code = @x_ap_payment_summary.vendor_code
and @x_job_cash_flow.invoice_number = @x_ap_payment_summary.invoice_number
and @x_job_cash_flow.invoice_type_code = @x_ap_payment_summary.invoice_type_code
update @x_job_cash_flow set invoice_ratio = total_amount / invoice_amount where invoice_amount <> 0
update @x_job_cash_flow set job_payment_amt = inv_payment_amt * invoice_ratio
select * from @x_job_cash_flow
return
November 26, 2008 at 8:27 am
You have to alias the table variable and use that in your declaration.
Use this instead:
UPDATE @x_job_cash_flow
SET inv_payment_amt = PS.inv_payment_amt
FROM @x_job_cash_flow CF
INNER JOIN @x_ap_payment_summary PS ON CF.company_code = PS.company_code
AND CF.vendor_code = PS.vendor_code
AND CF.invoice_number = PS.invoice_number
AND CF.invoice_type_code = PS.invoice_type_code
November 26, 2008 at 8:32 am
Thank you very much. That worked.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply