Strange Results... 
Author Message
 Strange Results...

Im running the following query and I don't understand why I do not get
a negative sign in the balance column.  the column curr_open_amt is
ACTUALLY negative, but it is GREATLY confusing to not SEE it display
negative unless CAST to character.

select a.customer_no, a.age_cat, a.curr_open_amt as balance,
sign(a.curr_open_amt) as Field_sign,
cast(a.curr_open_amt as char(11)) as Cast_balance
from ar_header a
where a.customer_no = '0005356000'
and a.cYear = 2002
and a.cMonth = 4
and a.curr_open_amt < 0
order by 1

Result set
==========
customer_no  age_cat     balance       Field_sign    Cast_balance
------------ ----------- ------------- ------------- ------------
5356000      6           10.14         1.00          -10.14    
5356000      3           19.96         1.00          -19.96    
5356000      2           171.65        1.00          -171.65    
5356000      1           556.00        1.00          -556.00    
5356000      1           951.27        1.00          -951.27    
5356000      1           73.78         1.00          -73.78    
5356000      1           401.28        1.00          -401.28    
5356000      1           91.77         1.00          -91.77    
5356000      1           675.64        1.00          -675.64    
5356000      2           773.23        1.00          -773.23    
5356000      1           417.71        1.00          -417.71    
5356000      1           773.92        1.00          -773.92

Table is Defined as:

CREATE TABLE [dbo].[AR_HEADER] (
        [INVOICE_NUM] [decimal](8, 0) NOT NULL ,
        [CUSTOMER_NO] [decimal](10, 0) NOT NULL ,
        [INVOICED_DATE] [smalldatetime] NOT NULL ,
        [ORIG_DOC_TYPE] [char] (2) NOT NULL ,
        [ORIG_DOC_NO] [decimal](8, 0) NOT NULL ,
        [ORIG_DOC_DATE] [smalldatetime] NOT NULL ,
        [ORIG_DOC_AMNT] [decimal](11, 2) NOT NULL ,
        [CURR_OPEN_AMT] [decimal](11, 2) NOT NULL ,
        [DATE_PYMT_DUE] [smalldatetime] NULL ,
        [LAST_PYMNT_DATE] [smalldatetime] NULL ,
        [AGE_CAT] [int] NOT NULL ,
        [DIVISION_NO] [int] NOT NULL ,
        [SHIPPING_WHSE] [int] NOT NULL ,
        [INVOICE_TERMS] [char] (2) NOT NULL ,
        [TIMES_OVER_CODE] [int] NOT NULL ,
        [STATUS_DATE] [smalldatetime] NULL ,
        [STATUS_WORD] [char] (2) NOT NULL ,
        [CANADIAN_DOLLARS] [char] (2) NOT NULL ,
        [cYear] [int] NOT NULL ,
        [cMonth] [int] NOT NULL
) ON [PRIMARY]



Sun, 07 Nov 2004 02:15:57 GMT
 Strange Results...

What is the table definition? Or what is the datatype of a.curr_open_amt?


Quote:
> Im running the following query and I don't understand why I do not get
> a negative sign in the balance column.  the column curr_open_amt is
> ACTUALLY negative, but it is GREATLY confusing to not SEE it display
> negative unless CAST to character.

> select a.customer_no, a.age_cat, a.curr_open_amt as balance,
> sign(a.curr_open_amt) as Field_sign,
> cast(a.curr_open_amt as char(11)) as Cast_balance
> from ar_header a
> where a.customer_no = '0005356000'
> and a.cYear = 2002
> and a.cMonth = 4
> and a.curr_open_amt < 0
> order by 1

> Result set
> ==========
> customer_no  age_cat     balance       Field_sign    Cast_balance
> ------------ ----------- ------------- ------------- ------------
> 5356000      6           10.14         1.00          -10.14
> 5356000      3           19.96         1.00          -19.96
> 5356000      2           171.65        1.00          -171.65
> 5356000      1           556.00        1.00          -556.00
> 5356000      1           951.27        1.00          -951.27
> 5356000      1           73.78         1.00          -73.78
> 5356000      1           401.28        1.00          -401.28
> 5356000      1           91.77         1.00          -91.77
> 5356000      1           675.64        1.00          -675.64
> 5356000      2           773.23        1.00          -773.23
> 5356000      1           417.71        1.00          -417.71
> 5356000      1           773.92        1.00          -773.92

> Table is Defined as:

> CREATE TABLE [dbo].[AR_HEADER] (
> [INVOICE_NUM] [decimal](8, 0) NOT NULL ,
> [CUSTOMER_NO] [decimal](10, 0) NOT NULL ,
> [INVOICED_DATE] [smalldatetime] NOT NULL ,
> [ORIG_DOC_TYPE] [char] (2) NOT NULL ,
> [ORIG_DOC_NO] [decimal](8, 0) NOT NULL ,
> [ORIG_DOC_DATE] [smalldatetime] NOT NULL ,
> [ORIG_DOC_AMNT] [decimal](11, 2) NOT NULL ,
> [CURR_OPEN_AMT] [decimal](11, 2) NOT NULL ,
> [DATE_PYMT_DUE] [smalldatetime] NULL ,
> [LAST_PYMNT_DATE] [smalldatetime] NULL ,
> [AGE_CAT] [int] NOT NULL ,
> [DIVISION_NO] [int] NOT NULL ,
> [SHIPPING_WHSE] [int] NOT NULL ,
> [INVOICE_TERMS] [char] (2) NOT NULL ,
> [TIMES_OVER_CODE] [int] NOT NULL ,
> [STATUS_DATE] [smalldatetime] NULL ,
> [STATUS_WORD] [char] (2) NOT NULL ,
> [CANADIAN_DOLLARS] [char] (2) NOT NULL ,
> [cYear] [int] NOT NULL ,
> [cMonth] [int] NOT NULL
> ) ON [PRIMARY]



Sun, 07 Nov 2004 02:24:23 GMT
 Strange Results...
Thanks for the table schema.

I simplified it a bit, and also loaded some sample data. Testing was done on
SQL Server 2000 w/SP2.

CREATE TABLE dbo.AR_HEADER (
CURR_OPEN_AMT decimal(11, 2) NOT NULL
)
go

insert into dbo.AR_HEADER values (-2)
insert into dbo.AR_HEADER values (-1)
insert into dbo.AR_HEADER values (0)
insert into dbo.AR_HEADER values (+1)
insert into dbo.AR_HEADER values (+2)

select a.curr_open_amt as balance
from dbo.AR_HEADER a
where a.curr_open_amt < 0

-- output from Query Analyzer:
balance
-------------
-2.00
-1.00

As you can see, SQL Server is indeed returning a signed value. I notice that
you did not list how you are querying the table. Try your query in Query
Analyzer, and I believe you will be able to confirm that SQL Server is
returning a signed value. In other words, I suspect that the problem lies
not with SQL Server but with either the query tool you are using, or more
likely, the presentation level.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> Im running the following query and I don't understand why I do not get
> a negative sign in the balance column.  the column curr_open_amt is
> ACTUALLY negative, but it is GREATLY confusing to not SEE it display
> negative unless CAST to character.

> select a.customer_no, a.age_cat, a.curr_open_amt as balance,
> sign(a.curr_open_amt) as Field_sign,
> cast(a.curr_open_amt as char(11)) as Cast_balance
> from ar_header a
> where a.customer_no = '0005356000'
> and a.cYear = 2002
> and a.cMonth = 4
> and a.curr_open_amt < 0
> order by 1

> Result set
> ==========
> customer_no  age_cat     balance       Field_sign    Cast_balance
> ------------ ----------- ------------- ------------- ------------
> 5356000      6           10.14         1.00          -10.14
> 5356000      3           19.96         1.00          -19.96
> 5356000      2           171.65        1.00          -171.65
> 5356000      1           556.00        1.00          -556.00
> 5356000      1           951.27        1.00          -951.27
> 5356000      1           73.78         1.00          -73.78
> 5356000      1           401.28        1.00          -401.28
> 5356000      1           91.77         1.00          -91.77
> 5356000      1           675.64        1.00          -675.64
> 5356000      2           773.23        1.00          -773.23
> 5356000      1           417.71        1.00          -417.71
> 5356000      1           773.92        1.00          -773.92

> Table is Defined as:

> CREATE TABLE [dbo].[AR_HEADER] (
> [INVOICE_NUM] [decimal](8, 0) NOT NULL ,
> [CUSTOMER_NO] [decimal](10, 0) NOT NULL ,
> [INVOICED_DATE] [smalldatetime] NOT NULL ,
> [ORIG_DOC_TYPE] [char] (2) NOT NULL ,
> [ORIG_DOC_NO] [decimal](8, 0) NOT NULL ,
> [ORIG_DOC_DATE] [smalldatetime] NOT NULL ,
> [ORIG_DOC_AMNT] [decimal](11, 2) NOT NULL ,
> [CURR_OPEN_AMT] [decimal](11, 2) NOT NULL ,
> [DATE_PYMT_DUE] [smalldatetime] NULL ,
> [LAST_PYMNT_DATE] [smalldatetime] NULL ,
> [AGE_CAT] [int] NOT NULL ,
> [DIVISION_NO] [int] NOT NULL ,
> [SHIPPING_WHSE] [int] NOT NULL ,
> [INVOICE_TERMS] [char] (2) NOT NULL ,
> [TIMES_OVER_CODE] [int] NOT NULL ,
> [STATUS_DATE] [smalldatetime] NULL ,
> [STATUS_WORD] [char] (2) NOT NULL ,
> [CANADIAN_DOLLARS] [char] (2) NOT NULL ,
> [cYear] [int] NOT NULL ,
> [cMonth] [int] NOT NULL
> ) ON [PRIMARY]



Sun, 07 Nov 2004 03:42:15 GMT
 Strange Results...
Besides providing more information, is it possible that the
"negative sign symbol" in Start|Settings|Control Panel|Regional Settings|Numbers
is not a minus sign, and that you have output that is displayed according
to the regional settings?

Steve Kass
Drew University

Quote:

> Im running the following query and I don't understand why I do not get
> a negative sign in the balance column.  the column curr_open_amt is
> ACTUALLY negative, but it is GREATLY confusing to not SEE it display
> negative unless CAST to character.

> select a.customer_no, a.age_cat, a.curr_open_amt as balance,
> sign(a.curr_open_amt) as Field_sign,
> cast(a.curr_open_amt as char(11)) as Cast_balance
> from ar_header a
> where a.customer_no = '0005356000'
> and a.cYear = 2002
> and a.cMonth = 4
> and a.curr_open_amt < 0
> order by 1

> Result set
> ==========
> customer_no  age_cat     balance       Field_sign    Cast_balance
> ------------ ----------- ------------- ------------- ------------
> 5356000      6           10.14         1.00          -10.14
> 5356000      3           19.96         1.00          -19.96
> 5356000      2           171.65        1.00          -171.65
> 5356000      1           556.00        1.00          -556.00
> 5356000      1           951.27        1.00          -951.27
> 5356000      1           73.78         1.00          -73.78
> 5356000      1           401.28        1.00          -401.28
> 5356000      1           91.77         1.00          -91.77
> 5356000      1           675.64        1.00          -675.64
> 5356000      2           773.23        1.00          -773.23
> 5356000      1           417.71        1.00          -417.71
> 5356000      1           773.92        1.00          -773.92

> Table is Defined as:

> CREATE TABLE [dbo].[AR_HEADER] (
>         [INVOICE_NUM] [decimal](8, 0) NOT NULL ,
>         [CUSTOMER_NO] [decimal](10, 0) NOT NULL ,
>         [INVOICED_DATE] [smalldatetime] NOT NULL ,
>         [ORIG_DOC_TYPE] [char] (2) NOT NULL ,
>         [ORIG_DOC_NO] [decimal](8, 0) NOT NULL ,
>         [ORIG_DOC_DATE] [smalldatetime] NOT NULL ,
>         [ORIG_DOC_AMNT] [decimal](11, 2) NOT NULL ,
>         [CURR_OPEN_AMT] [decimal](11, 2) NOT NULL ,
>         [DATE_PYMT_DUE] [smalldatetime] NULL ,
>         [LAST_PYMNT_DATE] [smalldatetime] NULL ,
>         [AGE_CAT] [int] NOT NULL ,
>         [DIVISION_NO] [int] NOT NULL ,
>         [SHIPPING_WHSE] [int] NOT NULL ,
>         [INVOICE_TERMS] [char] (2) NOT NULL ,
>         [TIMES_OVER_CODE] [int] NOT NULL ,
>         [STATUS_DATE] [smalldatetime] NULL ,
>         [STATUS_WORD] [char] (2) NOT NULL ,
>         [CANADIAN_DOLLARS] [char] (2) NOT NULL ,
>         [cYear] [int] NOT NULL ,
>         [cMonth] [int] NOT NULL
> ) ON [PRIMARY]



Sun, 07 Nov 2004 04:37:23 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. strange result when using the into statement

2. Strange result on a count(*)

3. Strange results from Stores Procedure

4. ISNULL and LEFT producing strange results

5. Strange results of Execution plan

6. Strange results from SQL select query

7. strange results in the profiler

8. contains and freetext functions give strange results

9. String comparison -> strange result

10. Strange Results With NULL Comparison in Stored Procedures

11. Strange results from query with MONEY variables in WHERE clause

12. Strange results with one execution plan


 
Powered by phpBB® Forum Software