September 9, 2008 at 3:47 pm
Problem at end of code:
If EXISTS (SELECT * FROM sysobjects WHERE name = 'TransHist' )
BEGIN
ALTER TABLE dbo.TransHist DROP CONSTRAINT pk_TransHist
DROP INDEX TransHist.IX_TransHist_AccountID_Date
DROP TABLE TransHist
END
-- ===== Create the test table
CREATE TABLE [dbo].TransHist
( RowNum INT IDENTITY (1,1) NOT NULL
,company int NOT NULL
,fiscal_year int NOT NULL
,acct_unit nvarchar(15) NOT NULL
,account int NOT NULL
,sub_account int NOT NULL
,posting_date datetime NOT NULL
,reference nvarchar(150) NOT NULL
,SystemCode nvarchar(2) NOT NULL
,TotBegDebits decimal(24,2) NOT NULL
,TotBegCredits decimal(24,2) NOT NULL
,TMONTH int NOT NULL
,account_desc nvarchar(150) NOT NULL
,acct_unitname nvarchar(50) NOT NULL
,update_date datetime NOT NULL
,debits decimal (18,2) NOT NULL
,credits decimal (18,2) NOT NULL
,BegBal MONEY NOT NULL
,RunBal MONEY NOT NULL
,TransDescr nvarchar(150) NOT NULL
,JE int NOT NULL
,StartDate datetime NOT NULL
,EndDate datetime NOT NULL )
ALTER TABLE dbo.TransHist
ADD CONSTRAINT pk_TransHist PRIMARY KEY CLUSTERED (RowNum)
CREATE NONCLUSTERED INDEX IX_TransHist_AccountID_Date
on dbo.TransHist (Account, Acct_unit, sub_account,posting_date)
SET IDENTITY_INSERT [dbo].TransHist ON
insert into [dbo].TransHist
(company
,fiscal_year
,acct_unit
,account
,sub_account
,posting_date
,reference
,SystemCode
,TotBegDebits
,TotBegCredits
,TMONTH
,account_desc
,acct_unitname
,update_date
,debits
,credits
,BegBal
,RunBal
,TransDescr
,JE
,StartDate
,EndDate
)
Select company
,fiscal_year
,acct_unit
,account
,sub_account
,isnull(posting_date,'') as posting_date
,reference
,SystemCode
,TotBegDebits
,TotBegCredits
,TMONTH
,account_desc
,acct_unitname
,update_date
,debits
,credits
,BegBal
,RunBal
,TransDescr
,JE
,StartDate
,EndDate
FROM @tmp_Details
--THE @PrevRunBal resets the previous running balance when account, sub, or acct_unit
-- changes ddr 9/8/08
DECLARE @PrevRunBal MONEY
SET @PrevRunBal = 0
DECLARE @PrevAcct int
SET @PrevAcct = ''
DECLARE @PrevSubAcct smallint
SET @PrevSubAcct = ''
DECLARE @PrevAcctUnit nvarchar(15)
SET @PrevAcctUnit = ''
UPDATE [dbo].TransHist
SET @PrevRunBal = RunBal = Case
WHEN Account = @PrevAcct and
Sub_Account = @PrevSubAcct and
acct_unit = @PrevAcctUnit
THEN @PrevRunBal
+ debits + credits
ELSE debits + credits
END,
@PrevAcct = account,
@PrevSubAcct = sub_account,
@PrevAcctUnit = acct_unit
FROM [dbo].TransHist WITH (INDEX (IX_TransHist_AccountID_Date) ,TABLOCKX)
The error says "Index 'IX_TransHist_AccountID_Date' on table 'dbo.TransHist' (specified in the FROM clause) does not exist." I don't get an error on the index creation; just this (for me) complex UPDATE statement. Does anyone have any thoughts on this? Thanks.
September 9, 2008 at 3:57 pm
Have you verified that the index does exist?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 10, 2008 at 6:07 am
Can you break it down and run a couple of steps at a time? I tried duplicating the script in miniature and it worked fine.
CREATE TABLE x ( id INT, dsc VARCHAR(50) )
CREATE NONCLUSTERED INDEX z ON x ( dsc )
SELECT *
FROM x WITH ( INDEX ( z ) )
DROP TABLE x
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 10, 2008 at 9:09 am
I finally got it to work but not with the code on running balances that's on the Net. I definitely had to make some code changes to get it to work but am happy with the results now.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply