Need help with Transaction Error 3624

[Toga]

Having an intermittent problem....

Doing a sum of 2 decimal columns that I mulitply together and I get a transaction error 3624. If I don't add them together, it works fine.

Sybase shows this as one of their solved cases (10884077) but unfortuantely our company doesn't subscribe to that service. Such help with errors ought to be free anyway but that's another story.

Anyway, does anyone know how to get around / fix this error.

Thanks for your help!

 

[JMCraig]

Sometimes with something like this, you can work around an error by doing what you want in several steps. For instance, you could try this:

1. Select the key values and these two decimal columns into a temp table
2. Alter the table to add a column for the product
3. Use an UPDATE to populate the new column with product of the two numbers
4. SELECT the SUM of the product column.

I've had a number of cases where this kind of approach got around a problem.

It does sound like your company needs to buy maintenance. Do you not have access to EBF downloads?

Good luck!

John

J M Craig
Alpha-G Consulting, LLC

http://www.alphagconsulting.com

nsjmcraig@netscape.net

 

[Toga]

John....Thank you for your response. Fortunately, I was aable to resolve the issue by the decimal function within my code to calculate StDev...."(dec(10,2),avg(MyFieldName))"

The next issue I ran into with figuring out how to calculate StDev was blank spaces in the data columns...ie no data. I got around that by adding an abs function to the area of my formula causing the problem.

Anyways.....here's the finished code. I'm not a programmer per se so maybe it's not as good as it could be but the result matches any data set I dump into excel and run the StDev.....where a function actually exist! Still can't believe Sybase wouldn't have included such a function....


select

(SQRT((sum((MyFieldName - (select convert(dec(10,2),avg(MyFieldName))From MyTableNameWhere MyWhereCriteria and abs(MyFieldName)>0))*(MyFieldName - (select convert(dec(10,2),avg(MyFieldName))From MyTableNameWhere MyWhereCriteria and abs(MyFieldName)>0)))) /(select count(MyFieldName)-1 from MyTableNamewhere MyWhereCriteria))) 'StDev'

from MyTableNameWHERE MyWhereCriteria