The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Currency - What Data Type should it be in ADO 
Posted: 28 Aug 07 11:52 AM
|
I am running NEW 7.0.1 Database and found that all the SLX Currency fields are of MSSQL Type Decimal (17,4) but all the Custom Currency Fields are Numeric 17,4.
Likewise the SLXProfiler reports the Numeric(17,4) Type as "DBTYPE is Unknown".
Has anyone seen this?
What SHOULD the types be and should I change them in the database and in my code?
Anyone seen this before? |
|
|
|
Re: Currency - What Data Type should it be in ADO
Posted: 28 Aug 07 3:24 PM
|
| We are running 7.0.1 on SQL Server 2005, and it looks to me like all of the standard fields are Numeric(17, 4). For custom fields I use Floats. Not sure if that is best practice or not, but it is the way I have been doing it for years, and have never had any issues. I would assume that either way would work. |
|
|
|
Re: Currency - What Data Type should it be in ADO
Posted: 29 Aug 07 6:11 AM
|
Fixed 17,4 is the best way to represent currency in SalesLogix.
At one time it was Fixed 15,2 and there were "precision" issues. Someone got the "bright" idea that going to float would solve that.. well.. it really makes it worse.
At the lowest level (processor instruction set) floating point arithmetic suffers from rounding errors. Example:
Do your product pricing in dollars/cents.. have a dozen or so items.. add them up in groups of 3 or 4 and than add that together.. now just all up all of them in one swoop... take each result and than diveide by the total number of items.. you will start to get "skewing"...
Computer Science 101 - floating point arithmetic is allways going to have rounding errors in either the "odd" or "even" side depending on how it was implemented. IBM had it right (in the mainframe days) - BCD arithmetic... no rounding errors.. no issues.
Fixed 17,4 gives us more precision and HELPS to reduce rounding errors.
You should change all of your custom currency fields to fixed 17,4 - we do.
--
rjl |
|
|
|
Re: Currency - What Data Type should it be in ADO
Posted: 29 Aug 07 8:43 AM
|
In the SLXProfiler are you seeing the Currency (Numeric 17,4) fields reported as [DBTYPE is Unknown] which is an indicator of a failure in sync, or are they reported as a defined type?
I'm actually writing followed be a select sum() and the select sum() comes back with zero all the time even though the valies are all there "Select Isnull(Sum(trans_Credit),0) from ... Where ...".
7.2 does it the same way SLX Currency fields are decimal (17,4), Custom fields are numeric (17,4).
When writing ADO Code to generate recordseets from code or move data around this becomes an issue with truncation and sync. |
|
|
|
Re: Currency - What Data Type should it be in ADO
Posted: 29 Aug 07 8:53 AM
|
Sorry RJ missed your post. I remember the bad old days of floats all too well, had to write salesorder is 3 crystal formats to avoid sumation totals after sync with Oracle, SQL & interbase.
Does Fixed 17,4 mean decimal 17,4 or numeric 17,4?
Is this oracle and sync compliant and provider compliant? Thats my real issue.
I'm writing an invoiceing batch the issuing updates to the participants of the batch resetting Amount Due/Paid/Remaining.
My write appears on the server side to write fine, but the Select IsNull(Sum(CurrenctField),0) brings back a zero.
If this works I'll di it, I just need to update a bunch of code and prefer doing in once.
Thanks |
|
|
| |
|
Re: Currency - What Data Type should it be in ADO
Posted: 30 Aug 07 8:44 AM
|
Interesting, SLX just reported SDK is reporting it as a defect.
This might be only a SLXProfiler reporting error, or something else.
My sum() problem appears to make it more complicated.
|
|
|
| |
| |
| |
|