log/log10 functions truncate my resultset

[BernsteinVsTheDb]

Hello all,

I have a table that contains 100+ rows. I'm trying to run a query that
looks like this:
SELECT ..., log10(...) as Rank FROM Table
and the resultset contains only the first row of results.

SELECT ..., log10(...) as Rank FROM Table ORDER BY Rank
returns no results at all!

If you replace log10 with log, it does the same thing. However, if you
replace log10 with abs, it returns exactly what you'd expect, all 100+
rows.

Any thoughts?

Thanks in advance!

[joeNOSPAM]

My guess is that the math the DBMS is doing blows up on the row
after the last one you get. Is the connection to the DBMS still OK?
Joe Weinstein at BEA Systems

[Erland Sommarskog]

BernsteinVsTheDb (Or***********@gmail.com) writes:

Hello all,

I have a table that contains 100+ rows. I'm trying to run a query that
looks like this:
SELECT ..., log10(...) as Rank FROM Table
and the resultset contains only the first row of results.

SELECT ..., log10(...) as Rank FROM Table ORDER BY Rank
returns no results at all!

If you replace log10 with log, it does the same thing. However, if you
replace log10 with abs, it returns exactly what you'd expect, all 100+
rows.

Are all the numbers you perform log/log10 on > 0?

Do you run the query from an application or from Query Analyzer? In
the latter case, you should get an error message.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dimitri Furman]

On Feb 24 2006, 06:09 pm, Erland Sommarskog <es****@sommarskog.se> wrote in
news:Xn*********************@127.0.0.1:

BernsteinVsTheDb (Or***********@gmail.com) writes:

Hello all,

I have a table that contains 100+ rows. I'm trying to run a query that
looks like this:
SELECT ..., log10(...) as Rank FROM Table
and the resultset contains only the first row of results.

SELECT ..., log10(...) as Rank FROM Table ORDER BY Rank
returns no results at all!

If you replace log10 with log, it does the same thing. However, if you
replace log10 with abs, it returns exactly what you'd expect, all 100+
rows.

Are all the numbers you perform log/log10 on > 0?

Funny thing is, I had this exact issue today while ranking results.
Log10(0) produces a "A domain error occured" warning message in QA and
causes an empty result set to be returned.

--
remove a 9 to reply by email