Problem Stored Proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem Stored Proc

Author

Topic

Noodles
Starting Member

5 Posts

Posted - 2003-03-03 : 18:49:48

Hi, i'm having problems with this stored procedure, i can't seem to figure out why it's not working. It keeps on giving me these errors:

Server: Msg 207, Level 16, State 3, Procedure UpdateCustomerOpeningBalanceTempTable_qry, Line 33
Invalid column name 'InvoiceTotal'.
Server: Msg 207, Level 16, State 1, Procedure UpdateCustomerOpeningBalanceTempTable_qry, Line 33
Invalid column name 'ReceiptTotal'.

Even though I've aliased columns as InvoiceTotal and ReceiptTotal

Here's my stored proc:

CREATE PROCEDURE dbo.UpdateCustomerOpeningBalanceTempTable_qry AS

CREATE TABLE #StatementRptInvoiceSummary_Temp (
CustomerNumber nvarchar(5),
Customer nvarchar(100),
InvoiceNumber nvarchar(6),
InvoiceDate datetime,
DiscountPercentage float,
DiscountAmount money,
ChargeTotal money,
Discount float,
NetTotal float,
GST float,
Total float,
SuppressOnStatements bit,
Chargeable bit)

INSERT #StatementRptInvoiceSummary_Temp
EXEC StatementRptInvoiceSummary_Qry

CREATE TABLE #StatementRptReceiptSummary_Temp (
CustomerNumber nvarchar(5),
ReceiptNumber nvarchar(6),
ReceiptDate datetime,
ReceiptType nvarchar(4),
Description nvarchar(50),
Amount money,
SuppressOnstatements bit)

INSERT #StatementRptReceiptSummary_Temp
EXEC StatementRptReceiptSummary_Qry

INSERT CustomerOpeningBalanceTemp_Tbl
SELECT CustomerBalance_Tbl.CustomerNumber, CustomerBalance_Tbl.Balance, coalesce(#StatementRptInvoiceSummary_Temp.Total, 0) AS InvoiceTotal, coalesce(Sum(#StatementRptReceiptSummary_Temp.Amount),0) AS ReceiptTotal, [Balance]-[InvoiceTotal]+[ReceiptTotal] AS OpeningBalanceCalc
FROM (#StatementRptInvoiceSummary_Temp RIGHT JOIN CustomerBalance_Tbl ON #StatementRptInvoiceSummary_Temp.CustomerNumber = CustomerBalance_Tbl.CustomerNumber) LEFT JOIN #StatementRptReceiptSummary_Temp ON CustomerBalance_Tbl.CustomerNumber = #StatementRptReceiptSummary_Temp.CustomerNumber
GROUP BY CustomerBalance_Tbl.CustomerNumber, CustomerBalance_Tbl.Balance, #StatementRptInvoiceSummary_Temp.Total
ORDER BY CustomerBalance_Tbl.CustomerNumber;
GO

Thanks in advance :)

gwhiz
Yak Posting Veteran

78 Posts

Posted - 2003-03-03 : 19:10:53

As you may have guessed you can't use an aliased column later in a query you must use the actual column name or formula.

Noodles
Starting Member

5 Posts

Posted - 2003-03-03 : 19:47:19

Thanks, i replaced out the column names with the formula and it works fine

thanks for your help :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources