While in Update. Urgent Please

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

Transact-SQL (2000)

While in Update. Urgent Please

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-02 : 00:48:29

The Existing Query is like follwoing
WHILE ((SELECT COUNT(*)
FROM #invoice_tmp)>0)
BEGIN

SET ROWCOUNT 1
SELECT @tran_no = tran_no ,
@account_no = account_no ,
@cost_center = cost_center ,
@afe_no = afe_no ,
@base_amt = base_amt ,
@slno = slno ,
@hard_comm_no = hard_comm_no ,
@STATUS_TMP = STATUS
FROM #invoice_tmp
ORDER BY hard_comm_no , tran_no, tran_date , slno

DELETE
FROM #invoice_tmp
WHERE @tran_no = tran_no AND
@account_no = account_no AND
@cost_center = cost_center AND
@afe_no = afe_no AND
@base_amt = base_amt AND
@slno = slno AND
@hard_comm_no = hard_comm_no AND
@STATUS_TMP = STATUS

IF ( @hc_prev <> @hard_comm_no)
SELECT @amt_prev = 0.00

UPDATE #RESULT_TMP
SET invoice_no = @tran_no
, out_amt = Base_amount - ( ISNULL(@BASE_AMT,0.00) + ISNULL(@amt_prev,0.00))
, upd_flag = 'Y'
, curr_status = CASE @STATUS_TMP
WHEN 'P' THEN 'PM'
WHEN 'F' THEN 'FM'
ELSE curr_status
END,
STATUS = CASE @STATUS_TMP
WHEN 'P' THEN 'Partially Matched'
WHEN 'F' THEN 'Fully Matched'
ELSE STATUS
END
WHERE @account_no = account_code AND
@cost_center = cost_center AND
@afe_no = afe_code AND
@hard_comm_no = key_value AND
upd_flag ='N' AND
curr_status IN ('FM','PM')
SET ROWCOUNT 0

SELECT @hc_prev = @hard_comm_no , @amt_prev = isnull(@amt_prev,0.00) + isnull(@base_amt,0.00)

END

I cahanged as like follows .Is it correct.

UPDATE A
SET a.invoice_no = B.tran_no
, out_amt = Base_amount - ISNULL(b.base_amt,0.00)-- + ISNULL(@amt_prev,0.00))
, upd_flag = 'Y'
, curr_status = CASE b.status
WHEN 'P' THEN 'PM'
WHEN 'F' THEN 'FM'
ELSE curr_status
END,
STATUS = CASE b.status
WHEN 'P' THEN 'Partially Matched'
WHEN 'F' THEN 'Fully Matched'
ELSE a.status
END
From #RESULT_TMP A, #invoice_tmp B
WHERE b.account_no = a.account_code AND
b.cost_center = a.cost_center AND
b.afe_no = a.afe_code AND
b.hard_comm_no = a.key_value AND
a.upd_flag ='N' AND
a.curr_status IN ('FM','PM')

DELETE
FROM #invoice_tmp

Is it correct .please help me very urgent.

Thanks
KK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-02 : 00:56:52

Why don't you test it out ? You have the necessary table and data to test it out. The best we can do is to verify the syntax and overall logic.

For testing, you can always use BEGIN TRAN . . . ROLLBACK TRAN or do this in your development environment.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-02 : 00:59:33

Yes khtan, i Tested, But I didn't get a correct result. I asking u is my logic correct or not..?

Thanks
KK

Kristen
Test

22859 Posts

Posted - 2006-06-02 : 06:50:58

I doubt you can be sure that the DELETE is deleting the same row as the UPDATE.

I would SELECT one unique value(s) from #invoice_tmp into @TempVariables, UPDATE that row (using WHERE clause and @TempVariables) and then DELETE that row (using WHERE clause and same @TempVariables).

I expect you can do this in a single UPDATE statement without the loop by appending something like:


, @amt_prev = CASE WHEN @hc_prev <> @hard_comm_no) 
    THEN @amt_prev = 0.00 
    ELSE @amt_prev 
    END

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources