On Mar 26, 7:16 pm, "Gord" <g...@kingston.netwrote:
I would like to know whether it is possible to change all the numeric
fields in certain tables to 'Decimal' type using code?
Before making such a wholesale change, look here:
http://allenbrowne.com/bug-08.html
I think the debate has moved on since that article was written and we
now seeing people recommending the DECIMAL data type.
Out of interest, do you agree with everything the articles says? I've
posted numerous rebuttals of the article in this groups which you
could google for. If you are genuinely interested in this subject then
perhaps we can discuss it further here. I hope you are not just
repeating something you've heard without checking the facts for
yourself first.
FWIW I've inadvertently made a misstatement on this topic which I'd
like to take this opportunity to correct:
http://groups.google.com/group/micro...45b50947e9839f
[quote]
>From "ADO Provider Properties and Settings"
(
http://msdn.microsoft.com/library/de...library/en-us/
dnac...
"NULL Collation Order: A Long value (read-only) that specifies where
Null values are collated (sorted). For the Microsoft Jet provider,
the
value is always 4, which indicates that null values are sorted at the
low end of the list."
If there was a sort order bug with *any* Jet data type where the NULL
collation order was violated, it would be far more significant that
the
one under discussion that affects only negative DECIMAL values. Such
a
bug, should one exist, would almost certainly warrant a MSDN bug
article in itself. However, there is no such article and there is no
evidence I know of to suggest the NULL collation order is violated
under [any] circumstances.
[Unquote]
Actually, I think there is a problem where Jet NULL collation gets
violated. Consider that the essence of the DECIMAL sort order bug is
that the correctly-sorted set of negative values appear in the wrong
position of the resultset i.e. at the high end rather than between the
set of zeros and the set of NULLs at the low end. For example:
SELECT BadSort.dec_data
FROM
(
SELECT 3000000020 AS dec_data FROM DropMe
UNION ALL
SELECT NULL FROM DropMe
UNION ALL
SELECT -3000000010 FROM DropMe
UNION ALL
SELECT 0 FROM DropMe
UNION ALL
SELECT 3000000005 FROM DropMe
UNION ALL
SELECT -3000000005 FROM DropMe
UNION ALL
SELECT 0 FROM DropMe
UNION ALL
SELECT 3000000010 FROM DropMe
UNION ALL
SELECT NULL FROM DropMe
UNION ALL
SELECT -3000000020 FROM DropMe
) AS BadSort
ORDER BY BadSort.dec_data DESC;
So the trick would appear to be to force the set of zeros and positive
numbers to appear first and I've seen this posted as a suggested
solution:
ORDER BY BadSort.dec_data >= 0, BadSort.dec_data DESC;
However, this causes the NULL values to appear at the high end of the
resultset! The same problem is also exhibited by data types other than
DECIMAL.
So Jet's NULL collation has been violated and it does not seem to have
been big news. Guess I was wrong about that, then :(
I suspect the behaviour is due to known issues with the Jet's YESNO
data type which doesn't fit well with SQL's three-value logic (Jet
would appear to treat an UNKNOWN logical result differently than a
NULL value). The problem can be corrected, of course, in the ORDER BY
clause by removing the possibility of an UNKNOWN result by explicitly
handling NULL e.g.
ORDER BY BadSort.dec_data >= 0 AND BadSort.dec_data IS NOT NULL,
BadSort.dec_data DESC;
I suspect, though, that such an expression in the ORDER BY clause
would not perform well. My feeling is that the most *efficient* way to
sort would be to cast as DOUBLE, assuming that limiting scale to 15
significant figures is acceptable e.g.
ORDER BY CDBL(BadSort.dec_data DESC;
Interestingly, Allen condemns this as "very inefficient, requiring
conversion at every row" but I don't think that is correct: Jet has a
very efficient means of promoting a column of data to a higher type,
including promoting DECIMAL to DOUBLE.
All that said, I rarely if ever need to sort a resultset into an
explicit order in SQL (being a set-based language; sets have no
inherent order) and sorting in the middleware (e.g. recordset.Sort)
sidesteps the engine's problem with sorting.
PS I would also like to point out that Allen has said Access 2007 "is
too buggy for serious use" (
http://allenbrowne.com/Access2007.html) so
I trust you will remind people of that at every mention of anything
Access 2007 related ;-)
Jamie.
--
