Ascii in Sum - 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

Transact-SQL (2000)

Ascii in Sum

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-09-26 : 06:04:33

Dear All,

The following code has written by somebody.
i can't understand here, what is the need of ascii.

SELECT 0,cost_center, account_no, curr_code, NULL, NULL,
SUM((ascii(drcr_code) - 67) * ROUND(tran_amt, @Decimal_tmp)), /* Debit Amount */
SUM((68 - ascii(drcr_code)) * ROUND(tran_amt, @Decimal_tmp)), /* Credit Amount */
@hostid_tmp

i have performance issue on this query. Is Ascii is the reason of the performance problem..? please clarify this issue

Thanks and regards
krishnakumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-26 : 06:14:03

Where is your GROUP BY?
SQL summation is NOT like sums in Excel.

SELECT		0,
		cost_center,
		account_no,
		curr_code,
		NULL,
		NULL,
		(ascii(drcr_code) - 67) * ROUND(tran_amt, @Decimal_tmp),
		(68 - ascii(drcr_code)) * ROUND(tran_amt, @Decimal_tmp),
		@hostid_tmp
from		yourtable

Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-09-26 : 06:14:55

THIS IS THE QUERY
INSERT INTO G4_cc_acno_temp (
serial_no, costcentre, accountno, currcode,
costdesc, accntdesc, debit, credit,
host_id)
SELECT 0,cost_center, account_no, curr_code, NULL, NULL,
SUM((ascii(drcr_code) - 67) * ROUND(tran_amt, @Decimal_tmp)), /* Debit Amount */
SUM((68 - ascii(drcr_code)) * ROUND(tran_amt, @Decimal_tmp)), /* Credit Amount */
@hostid_tmp
FROM gl_voucher_detail_dup (NOLOCK)
WHERE company_code = @Company
AND location_code = @locn_code
-- AND gl_status in ('2','3')
AND gl_status ='3'--Added By Sankar[G4S] on 09/03/2006 as instructed by Mr Balbir
AND accounting_date BETWEEN @FromDate AND @ToDate
AND SUBSTRING(cost_center,3,4) BETWEEN LTRIM(RTRIM(@FromCost)) AND LTRIM(RTRIM(@ToCost))
GROUP BY cost_center, account_no, curr_code
/* Code added by Rekha on 08-11-01 for failure id.28391begins here */

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-26 : 06:17:55

I don't think it is SUM that is slow. It might be

1) Current indexes on the G4_cc_acno_temp table.
2) The WHERE clause SUBSTRING(cost_center,3,4) BETWEEN LTRIM(RTRIM(@FromCost)) AND LTRIM(RTRIM(@ToCost))

Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-09-26 : 06:22:04

The Table has Nonclustered index on company_code location_code AND accounting_date. There Is Nomore nonclustered index.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources