Query Analyzer shows negative numbers as positive numbers

[pj]

Why does M$ Query Analyzer display all numbers as positive, no matter
whether they are truly positive or negative ?

I am having to cast each column to varchar to find out if there are
any negative numbers being hidden from me :(

I tried checking Tools/Options/Connections/Use Regional Settings both
on and off, stopping and restarting M$ Query Analyer in betwixt, but
no improvement.

Am I missing some other option somewhere ?

[Anith Sen]

Re: Query Analyzer shows negative numbers as positive numbers

Are you using seeing this with both Output to Text & Output to Grid options?

--
--- Anith

[Erland Sommarskog]

Re: Query Analyzer shows negative numbers as positive numbers

pj (peterjohannsen @hotmail.com) writes:[color=blue]
> Why does M$ Query Analyzer display all numbers as positive, no matter
> whether they are truly positive or negative ?
>
> I am having to cast each column to varchar to find out if there are
> any negative numbers being hidden from me :(
>
> I tried checking Tools/Options/Connections/Use Regional Settings both
> on and off, stopping and restarting M$ Query Analyer in betwixt, but
> no improvement.
>
> Am I missing some other option somewhere ?[/color]

I have never had any problems with QA not showing negative numbers.

If you run your query from command-line ISQL, do you seen negative
numbers there?


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[pj]

Re: Query Analyzer shows negative numbers as positive numbers

"Anith Sen" <anith@bizdatas olutions.com> wrote in message news:<7wMgb.440 7$Eo2.1866@news read2.news.atl. earthlink.net>. ..[color=blue]
> Are you using seeing this with both Output to Text & Output to Grid options?[/color]

The field where I last noticed this is a decimal(15,2), so I
tried to simplify things and address your question as follows

********

create table atestneg (dollars decimal(15,2) null)
go
insert into atestneg (dollars) values (1)
go
insert into atestneg (dollars) values (-1)
go
insert into atestneg (dollars) values (-2)
go

select * from atestneg order by dollars

I see them, in grid view, with negative signs for the first two correctly.

Then I run an insert similar to this:

insert into atestneg(dollar s)
select my_real_values from my_real_table
where my_real_values< 0

select * from atestneg order by dollars


Now there are no negative signs, and the numbers appear to
go from highest to lowest, and then a 1 after the last one
(because of course they are really all negative except for
that last one).

I switched from "Results to grid" to "Results to text",
but still no negative signs.

I switched to "Results to file", ran it, gave a file name,
(drilled through a lot of annoying directories with spaces),
and found the file, and still no negative signs.


I tried instead, in Grid output mode now, this query

select * from atestneg where dollars<0 order by dollars

I still see the same thing (all my dollars descending except
for that trailing 1 which is really positive).

Copying & pasting from the grid output to OpenOffice Spreadsheet
or Microsoft Excel yields me a lot of numbers which still
look positive (and probably are, after the paste).


I wonder why the first three manually injected rows didn't
yield this problem behavior, but then adding a lot of
negative numbers to the same table did.

[pj]

Re: Query Analyzer shows negative numbers as positive numbers

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns940E62 C4A482Yazorman@ 127.0.0.1>...[color=blue]
> pj (peterjohannsen @hotmail.com) writes:[color=green]
> > Why does M$ Query Analyzer display all numbers as positive, no matter
> > whether they are truly positive or negative ?
> >
> > I am having to cast each column to varchar to find out if there are
> > any negative numbers being hidden from me :(
> >
> > I tried checking Tools/Options/Connections/Use Regional Settings both
> > on and off, stopping and restarting M$ Query Analyer in betwixt, but
> > no improvement.
> >
> > Am I missing some other option somewhere ?[/color]
>
> I have never had any problems with QA not showing negative numbers.
>
> If you run your query from command-line ISQL, do you seen negative
> numbers there?[/color]

I get nothing back with ISQL (using "select * from atestneg" from
my example posted in the other thread), but if I use OSQL, then
"select * from atestneg where dollars<0 order by dollars" yields me
the list of apparently positive numbers in descending order -- so
whatever ordered them understood that they were negative, but I don't
know if the client is getting them as negative and just showing
them incorrectly, or if the client is misunderstandin g them as
positive.

[Erland Sommarskog]

Re: Query Analyzer shows negative numbers as positive numbers

pj (peterjohannsen @hotmail.com) writes:[color=blue]
> Then I run an insert similar to this:
>
> insert into atestneg(dollar s)
> select my_real_values from my_real_table
> where my_real_values< 0
>
> select * from atestneg order by dollars[/color]

Since we don't have you real table and values it is difficult to
reproduce. Could you try this script:

drop table atestneg
go
create table atestneg (dollars decimal(15,2) null)
go
insert into atestneg (dollars) values (1)
go
insert into atestneg (dollars) values (-1)
go
insert into atestneg (dollars) values (-2)
go
insert into atestneg (dollars)
select Freight - 10
FROM Northwind..Orde rs
where Freight < 10
go
select * from atestneg order by dollars

and report what you see. (Preferably by including the output as
attachment.) Please run both in Query Analyzer and in OSQL and ISQL.
To run from the latter, save in a file and say:

ISQL -E -d tempdb -n -i atestneg.sql > atestneg.out

You mentioned that you did not get any output from ISQL, this sounds
funny.

Also if you can, try running against the same SQL Server from different
machines, and try running with your machine against other SQL Servers.

I have no idea of what's going on. But it seems that either something
is very funky with your data, or something is very funy with either
the server or client machine. I have actually seen an example of the latter,
although in that situation the problem was that the decimal point appeared
to be ignored on input. (This culprit then was running SQL Mail with
Outlook as mail client, if I remember correctly.)

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[horton]

Re: Query Analyzer shows negative numbers as positive numbers

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns940F4B E2D4C2Yazorman@ 127.0.0.1>...[color=blue]
> pj (peterjohannsen @hotmail.com) writes:[color=green]
> > Then I run an insert similar to this:
> >
> > insert into atestneg(dollar s)
> > select my_real_values from my_real_table
> > where my_real_values< 0
> >
> > select * from atestneg order by dollars[/color]
>
> Since we don't have you real table and values it is difficult to
> reproduce. Could you try this script:
>
> drop table atestneg
> go
> create table atestneg (dollars decimal(15,2) null)
> go
> insert into atestneg (dollars) values (1)
> go
> insert into atestneg (dollars) values (-1)
> go
> insert into atestneg (dollars) values (-2)
> go
> insert into atestneg (dollars)
> select Freight - 10
> FROM Northwind..Orde rs
> where Freight < 10
> go
> select * from atestneg order by dollars
>
> and report what you see. (Preferably by including the output as
> attachment.) Please run both in Query Analyzer and in OSQL and ISQL.
> To run from the latter, save in a file and say:[/color]


Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'atestneg', because it does not exist in the system catalog.

(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Northwind..Ord ers'.


Obviously this is going to fail in isql & osql as well,
because of the reference to the nonexistent table Northwind..Orde rs.
:(

[Erland Sommarskog]

Re: Query Analyzer shows negative numbers as positive numbers

horton (horton_ellas@y ahoo.com) writes:[color=blue]
> Erland Sommarskog <sommar@algonet .se> wrote in message[/color]
news:<Xns940F4B E2D4C2Yazorman@ 127.0.0.1>...[color=blue][color=green]
>> Since we don't have you real table and values it is difficult to
>> reproduce. Could you try this script:
>>
>> drop table atestneg
>> go
>> create table atestneg (dollars decimal(15,2) null)
>> go
>> insert into atestneg (dollars) values (1)
>> go
>> insert into atestneg (dollars) values (-1)
>> go
>> insert into atestneg (dollars) values (-2)
>> go
>> insert into atestneg (dollars)
>> select Freight - 10
>> FROM Northwind..Orde rs
>> where Freight < 10
>> go
>> select * from atestneg order by dollars
>>
>> and report what you see. (Preferably by including the output as
>> attachment.) Please run both in Query Analyzer and in OSQL and ISQL.
>> To run from the latter, save in a file and say:[/color]
>
>...
>
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Northwind..Ord ers'.
>
>
> Obviously this is going to fail in isql & osql as well,
> because of the reference to the nonexistent table Northwind..Orde rs.
>:([/color]

Northwind is an example database that ships with SQL Server since
version 7. If you don't have Northwind..Orde rs on your server, you
are either running SQL 6.5, or you have for some reason removed
Northwind from the server or at least dropped the Orders table in
it.

In in the Install directory for SQL Server you can find the file
instnwnd.sql that installs Northwind.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp