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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Indexing.
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CSK
Constraint Violating Yak Guru

USA
489 Posts

Posted - 05/25/2006 :  00:37:29  Show Profile  Send CSK a Yahoo! Message  Reply with Quote
Dear All.
Here i am using the temp table. But i can't take a decission put an index for this table. i am updating some what , here i present that.
please suggest me the index for the table #tmp_one

CREATE TABLE #tmp_one
( sno smallint null,
h01_emp_num EMPNUM null,
amount varchar(255) null,
head varchar(255) null,
elhead varchar(255) null,
total varchar(255) null,
page_total cmn_pamt null,
element varchar(8) null,
tempamount cmn_pamt null,
amountlen smallint null,
stramount varchar(15) null,
sumstr varchar(15) null
)

*********************************************************************************************

UPDATE #tmp_one
SET head = SPACE(@extractlength - 8) + 'EARNINGS'
WHERE sno = @sno

UPDATE #tmp_one
SET page_total = 0
WHERE sno = @sno

UPDATE #tmp_one
SET amount = RTRIM('')
, head = RTRIM('')
, elhead = RTRIM('')
, total = RTRIM('')
, element = RTRIM('')
, stramount = RTRIM('')
, sumstr = RTRIM('')
WHERE sno = @sno


UPDATE #tmp_one
SET head = SPACE(@extractlength - 5) + 'TOTAL'
WHERE sno = @sno

UPDATE #tmp_one
SET elhead = SPACE(@extractlength - 9) + 'CAR. OVER'
WHERE sno = @sno

UPDATE #tmp_one
SET amount = STUFF(STR(#t4.page_total, @extractlength, @pamt),@extractlength - 3 - @pamt,0,',')
FROM #tmp_four #t4
WHERE #t4.sno = @sno
AND #tmp_one.H01_Emp_Num = #t4.H01_Emp_Num
AND DATALENGTH(LTRIM(STR(#t4.page_total,10,2))) > 4 + @pamt
AND DATALENGTH(LTRIM(STR(#t4.page_total,10,2))) < 8 + @pamt
AND #tmp_one.sno = #t4.sno

UPDATE #tmp_one
SET amount = STR(#t4.page_total, @extractlength - 2, @pamt)
FROM #tmp_four #t4
WHERE #t4.sno = @sno
AND #tmp_one.H01_Emp_Num = #t4.H01_Emp_Num
AND #tmp_one.sno = #t4.sno

UPDATE #tmp_one
SET amount = STUFF(amount,1,0,' ')
WHERE DATALENGTH(LTRIM(amount)) < 5 + @pamt
AND #tmp_one.sno = @sno

UPDATE #tmp_one
SET amount = STUFF(amount,@extractlength - 5 - @pamt,0,',')
WHERE DATALENGTH(LTRIM(amount)) > 4 + @pamt
AND #tmp_one.sno = @sno

UPDATE #tmp_one
SET amount = STUFF(amount,1,0,' ')
WHERE DATALENGTH(LTRIM(amount)) > 4 + @pamt
AND DATALENGTH(LTRIM(amount)) < 9 + @pamt
AND #tmp_one.sno = @sno

UPDATE #tmp_one
SET amount = STUFF(amount,@extractlength - 8 - @pamt,0,',')
WHERE DATALENGTH(LTRIM(amount)) > 8 + @pamt
AND #tmp_one.sno = @sno

SELECT @noofcol = @noofcol + 1

UPDATE #tmp_one
SET head = head + SPACE(@extractlength - 8) + 'EARNINGS'
WHERE sno = @sno

UPDATE #tmp_one
SET page_total = #tmp_four.page_total
FROM #tmp_four
WHERE #tmp_four.sno = @sno
AND #tmp_one.H01_Emp_Num = #tmp_four.H01_Emp_Num

UPDATE #tmp_one
SET elhead = elhead + SPACE(@extractlength - @len) + LTRIM(RTRIM(@element))
WHERE sno = @sno

UPDATE #tmp_one
SET element = @element
WHERE sno = @sno

/*Code added by bala - to nullify the tempamount before updating the value - because in case if there is no value in p21, then the previous value (for the previous element remain there)*/
UPDATE #tmp_one
SET tempamount = 0.0

UPDATE #tmp_one
SET tempamount = ISNULL(T3.P21_Actual_Rate,0.0)
FROM #tmp_three T3, #tmp_one T1
WHERE T1.H01_emp_num = T3.H01_Emp_num
AND T3.P09_Element_Code = T1.element
AND T1.sno = @sno

UPDATE #tmp_one
SET amountlen = DATALENGTH(LTRIM(RTRIM(STR(tempamount,@extractlength,@pamt))))
WHERE sno = @sno

UPDATE #tmp_one
SET stramount = STR(tempamount,@extractlength - 2,@pamt)
WHERE sno = @sno

UPDATE #tmp_one
SET stramount = STUFF(stramount,1,0,' ')
WHERE sno = @sno
AND amountlen < 5 + @pamt

UPDATE #tmp_one
SET stramount = STUFF(stramount,@extractlength - 5 - @pamt,0,',')
WHERE sno = @sno
AND amountlen > 4 + @pamt

UPDATE #tmp_one
SET stramount = STUFF(stramount,1,0,' ')
WHERE sno = @sno
AND amountlen > 4 + @pamt
AND amountlen < 8 + @pamt

UPDATE #tmp_one
SET stramount = STUFF(stramount,@extractlength - 8 - @pamt,0,',')
WHERE sno = @sno
AND amountlen > 7 + @pamt

UPDATE #tmp_one
SET amount = amount + stramount
WHERE sno = @sno

UPDATE #tmp_one
SET page_total = page_total + tempamount
WHERE sno = @sno


Thanks
KK

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 05/25/2006 :  01:05:24  Show Profile  Send jen a Yahoo! Message  Reply with Quote
sno

--------------------
keeping it simple...
Go to Top of Page

CSK
Constraint Violating Yak Guru

USA
489 Posts

Posted - 05/25/2006 :  01:11:31  Show Profile  Send CSK a Yahoo! Message  Reply with Quote
thanks jen

Thanks
KK
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 05/25/2006 :  06:20:41  Show Profile  Reply with Quote
quote:
UPDATE #tmp_one
SET head = SPACE(@extractlength - 8) + 'EARNINGS'
WHERE sno = @sno

UPDATE #tmp_one
SET page_total = 0
WHERE sno = @sno

UPDATE #tmp_one
SET amount = RTRIM('')
, head = RTRIM('')
, elhead = RTRIM('')
, total = RTRIM('')
, element = RTRIM('')
, stramount = RTRIM('')
, sumstr = RTRIM('')
WHERE sno = @sno



You can also combined most of the UPDATE statement into single statement. And you don't need to perform rtrim('') on empty string.

UPDATE #tmp_one
SET    head       = SPACE(@extractlength - 8) + 'EARNINGS',
       page_total = 0,
       amount     = '',
       head       = '',
       elhead     = '',
       total      = '',
       element    = '',
       stramount  = '',
       sumstr     = ''
WHERE  sno = @sno




KH


Edited by - khtan on 05/25/2006 06:21:42
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000