Sorry Peso - 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

Site Related Forums

The Yak Corral

Sorry Peso

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-16 : 05:20:52

sorry sorry sorry x1000

not that i wan to break the golden rules(set based>cursor)...but i jz cant help about it...i did question the problem...but..sometime it jz won happen...if not im dead(Repeat half year in training with usd each month* 140)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 05:29:37

Why are you excusing yourself?
I just wrote

quote:
Originally posted by Peso

So what are you asking for, really?
You write you can't get rid of CURSOR so you are stuck with it.

here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127577&whichpage=2

What you CAN do for possibly improving the speed of your procedure with the CURSOR, is this attempt below.
The basics are that you fetch all your records including joins in one step, store the result in a temp-table and THEN apply the CURSOR over the temp table.

...
DECLARE @x INT

CREATE TABLE #Temp (st_code {datatype here}, st_desc {datatype here}, total_rm {datatype here}, total_cn {datatype here}, qty {datatype here})

insert #temp
SELECT m.st_code, m.st_desc, th.total_rm*th.forex_rate as total_rm, th.total_cn*th.forex_rate as total_cn, th.qtt_out - th.quantity as qty
FROM st_mast m
inner join
(SELECT st_code, t.trx_type, t.qtt_out, t.quantity, t.total_price, 
CASE WHEN h.forex_rate = 0 then 1 else h.forex_rate END as forex_rate,
CASE WHEN t.trx_type IN('CN','CNC','GRO') then 0 else t.total_price END as total_rm,
CASE WHEN t.trx_type IN('CN','CNC','GRO') then t.total_price else 0 END as total_cn
FROM st_trx t 
right outer join (
SELECT CASE WHEN trx_type in('DO','CDO','DOL') then do_no
WHEN trx_type in('INV','CS','CN','DN','POS','INC','CNC') then in_no END as ref_no,
CASE WHEN trx_type in('DO','CDO','DOL') then (CASE WHEN in_date IS NULL or in_date='' then do_date else in_date END)
WHEN trx_type in('INV','CS','CN','DN','POS','INC','CNC') then in_date END as date,
forex_rate,
trx_type
FROM st_head)h on t.trnx_ref = h.ref_no and t.trx_type=h.trx_type
WHERE date>='2007-01-01 00:00:00.000' and date<='2007-12-31 00:00:00.000')th
on m.st_code=th.st_code
WHERE m.st_code>='' and m.st_code<='zzzzzzzzz'

DECLARE STK_TRX_CURSOR CURSOR FOR
SELECT st_code, st_desc, total_rm, total_cn, qty
FROM #Temp
ORDER BY st_code

BEGIN
...

WHILE @@FETCH_STATUS = 0
BEGIN
	...
END

FETCH NEXT FROM STK_TRX_CURSOR
INTO @st_code, @st_desc, @tov_total, @cn_total, @qty_total
END

CLOSE STK_TRX_CURSOR
DEALLOCATE STK_TRX_CURSOR

PRINT CAST('Grand total of report' as char(50))+'TOV'+CAST('' as char(100))+CAST(CONVERT(DECIMAL(14,2),@tov_Gtotal) as char(14))
PRINT CAST('' as char(5))+CAST('' as char(45))+'CNV'+CAST('' as char(100))+CAST(CONVERT(DECIMAL(14,2),@cn_Gtotal) as char(14))
PRINT CAST('' as char(5))+CAST('' as char(45))+'QTY'+CAST('' as char(100))+CAST(CONVERT(DECIMAL(14,2),@qty_Gtotal) as char(14))

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-16 : 05:34:16

quoto wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 05:38:26

Huh? I don't know what "quoto wrong" means.

In the suggestion above, I shortened the code by writing "..." instead of large portions of your original code.
These sections remains intact. The altered pieces are shown above, together with some untouched section so that you will know where to edit your code.

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-16 : 05:42:02

erm...actually not excusing la....jz that i feel u getting angry coz of me >"<

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 05:57:11

It's probably a communication thing, I am not mad at you.
I have to guess almost every word you write.

"la", "jz", "u", "urs", "coz" and "comp" are not words in my standard vocabulary.

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-16 : 06:00:26

woops tat was malaysia+english...we like to add in Malays, Chinese, Hokien, Cantonese to english
sorry sorry

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-06-16 : 06:23:18

how about in the public forum you don't add those to english so everyone can understand.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-16 : 10:38:46

I guess i am one of the few around that understand this

KH
[spoiler]Time is always against us[/spoiler]

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2009-06-16 : 11:50:43

it reminds me of l33t :)

-----------

ASCII and ye shall receive.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-06-16 : 13:57:44

http://www.google.com/intl/xx-hacker/

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-16 : 21:46:57

ya i shdn use too many manglish >"< so sorry

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-16 : 22:46:34