Re: Inconsistent negative numerical values in SQL2K database table

From: Steve Kass (skass_at_drew.edu)
Date: 02/03/05 

Date: Thu, 03 Feb 2005 01:14:14 -0500

Earl,

  There are probably some values for which the sign byte of the
decimal value is something other than 01 or 00. See the explanation
here:

http://groups.google.co.uk/groups?q=218AE3EB-60D7-4E5E-A2F1

You should be able to find the values with the funky sign and fix them
as shown in the above thread like this:

update T set
  decVal = 1*decVal
where [if a large table, identify just the ones that have bad signs if
possible]

or something similar if the values should be negative. A handful of
data providers seem to cause this problem, but I don't recall seeing it
between SQL Servers before. How did you move the data from
one to the other?

Steve Kass
Drew University

Earl Newcomer wrote:

>I have a case where the sum of a column containing positive, zero and
>negative values is correct in Query Analyzer but the values in the column do
>not contain a '-' (minus sign). The rows sort as expected but they do not
>have a minus sign. I can update a positive row value by multiplying it by -1
>and that value shows with a minus sign. The newly updated row sorts with the
>other negative values but it is the only row with a minus sign.
>
>This "condition" came to light when a cube built on the fact table (where
>this is occurring) was not summing correctly. The cube sees all values as
>positive as if an absolute function was being used. When the one positive
>row was updated to a negative value as described above, the cube recognized
>the negative value and the measure changed accordingly. What is it in the
>SQL Server table or in the cube interpretation of the table values that is
>causing this? Is there a workaround?
>
>Another twist. The table, pmix_fact1, with which we see this behavior was
>copied from another SQL Server 2000 server. The cubes built on that table
>sum correctly. I suspect the process of copying the table may be introducing
>this behavior. Has anyone experienced anything like this?
>
>I have seen this once before when using an ODBC driver to copy table rows
>from an Oracle 7.3.4 database to SQL Server 2000 where the values were all
>positive after the copy. I changed to the latest Oracle driver (9.0.1) that
>would still work with Oracle 7.3.4 and this resolved that problem. I have
>not seen a case where a SQL Server to SQL Server copy would confuse numeric
>values.
>
>Sample data from SQL Server 2000 SP3 Query Analyzer
>Extended_amount (sorted descending) No minus sign at the bottom.
>8.99
>7.99
>4.08
>3.19
>2.99
>2.98
>2.59
>2.29
>2.19
>2.19
>1.99
>1.79
>1.59
>1.49
>.69
>.00
>.00
>.00
>.00
>.00
>.00
>.00
>.00
>.00
>.00
>.00
>.00
>2.29
>2.98
>2.98
>3.19
>4.08
>4.08
>------
>27.43
>
>The cube reports: 66.63
>
>Update the 8.99 to 8.99 * -1 and the Query Analyzer results are:
>Extended_amount
>7.99
>4.08
>3.19
>2.99
>2.98
>2.59
>2.29
>2.19
>2.19
>1.99
>1.79
>1.59
>1.49
>.69
>.00
>.00
>.00
>.00
>.00
>.00
>.00
>.00
>.00
>.00
>.00
>.00
>2.29
>2.98
>2.98
>3.19
>4.08
>4.08
>-8.99
>------
>9.45
>
>The cube reports: 48.65
>
>Suggestions are appreciated.
>
>Earl Newcomer
>
>
>
>
>

Relevant Pages

  • Inconsistent negative numerical values in SQL2K database table
    ... not contain a '-' (minus sign). ... This "condition" came to light when a cube built on the fact table (where ... SQL Server table or in the cube interpretation of the table values that is ... Sample data from SQL Server 2000 SP3 Query Analyzer ...
    (microsoft.public.sqlserver.server)
  • OLAP not recognizing negative SQL Server table values
    ... not contain a '-' (minus sign). ... This "condition" came to light when a cube built on the fact table (where ... SQL Server table or in the cube interpretation of the table values that is ... Sample data from SQL Server 2000 SP3 Query Analyzer ...
    (microsoft.public.sqlserver.olap)
  • Re: long processing time
    ... The AS Operations Guide was just recently posted and got dropped from their ... >> 1) Have you ran the Optimize Schema wizard in the Cube Editor? ... >> inner join between the fact table and *all* of the dimension tables. ... >>> through SQL server. ...
    (microsoft.public.sqlserver.olap)
  • Re: long processing time
    ... currently it is processing 2 partitions in parallel. ... >1) Have you ran the Optimize Schema wizard in the Cube ... >the RDBMS (SQL Server or Oracle), ... >> The cube which took 9 days to process has time dimension ...
    (microsoft.public.sqlserver.olap)
  • Re: how to hold PCBs at right angles to each other?
    ... They need to be at 90 degrees plus or minus a tenth of a ... Initial production numbers will be fairly low so ... How about a cube of G10? ... you can have a 3D circuit board. ...
    (sci.electronics.design)