Abs Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Abs Function

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-02-26 : 06:47:14

Hi I'm tyring to incorporate a Max abs function into my query, but am getting a problem.

The reason why I am using the abs function is that i am performing a min and max check on a total value which can contain negative as well as positive amounts. If I run Max(total_invoice) I can return
-243232.56 as Max, where as the Max should be 343435.89.


select (case when isnumeric(total_invoice) = 1 
			 then max(total_invoice)
			 else max(abs(total_invoice)) 
		End) as Testing
from Table_1

Msg 8118, Level 16, State 1, Line 1
Column 'Table_1.Total_Invoice' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

I do not want a group by, just simply display the max value. I need to do the isnumeric check, as I can run this script on other columns which are non-numeric.

Thanks

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-02-26 : 06:58:27

[code]
Select MAx(a.Total_Invoice )
From
(
Select Abs(total_invoice) Total_Invoice
From Table_1
) As a
[/code]

Chirag

http://chirikworld.blogspot.com/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-02-26 : 07:00:28

or May be


Select Max(abs(total_invoice)) 
From Table_1

Chirag

http://chirikworld.blogspot.com/

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-02-26 : 07:07:46

Thanks, but I need the isnumeric function to be incorporated to my select statement. This is because the statement maybe used for non-numeric values as well.

My Audit Sample script:


		select 'SupplieR_table'as Table_Name, 
		count(*) as Total_Count,
		'Supplier_SRN'as Column_Name, 
		sum(case when cast([Supplier_SRN] as varchar) is not null and cast([Supplier_SRN] as varchar) <> '' then 0 else 1 end) as Blank,
		left(cast(sum(case when cast([Supplier_SRN] as varchar) is not null and cast(Supplier_SRN] as varchar) <> '' then 0 else 1 end) as numeric)
		/ 30401 * 100,5) as Percentage_Blank,
		count(distinct [DTect_Supplier_SRN]) as [Distinct],
		left(cast(count(distinct [DTect_Supplier_SRN]) as numeric)/30401 * 100,5) as Percentage_Distinct,
		min(len(cast([Supplier_SRN] as varchar))) as Min_length, 
		max(len(cast([Supplier_SRN] as varchar))) as Max_Length,
		cast(min([Supplier_SRN]) as varchar) as Min_Value,
		cast(max([Supplier_SRN]) as varchar) as Max_Value, 
--HERE - I need a case statement with isnumeric and abs function here...
		sum(case when [Supplier_SRN] is null then 1 else 0 end) as [Nulls],
		getdate() as Date_Run
		from SupplieR_table

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-02-26 : 07:14:48

[code]
Max(
Case When isnumeric(total_invoice) = 1
Then total_invoice
Else
abs(total_invoice)
End
)
[/code]

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-26 : 07:33:13

Beware, ISNUMERIC() doesn't always returns correct output.
Check this: [url]http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-02-26 : 07:51:05

Hi Chirag

THanks for the reply...nearly there....

When I try and run this for a non-numeric value i.e. address line then I get the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

I have tried casting it, but still get the same error.

Thanks!

p.s nice blog - useful scripts there! keep them coming :)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-02-26 : 07:58:57

Well, if you got the Text character then i Dont think you will be able to use max funtion as they
purely works on the numeric values.

for avoiding it, you have to use IsNumber function in your where clause ..
Somthing like this

Where
isnumeric(total_invoice) = 1

Chirag

http://chirikworld.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-26 : 09:30:42

select max(case when isnumeric(total_invoice) = 1
then abs(total_invoice)
else total_invoice
End) as Testing
from Table_1

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-02-26 : 09:39:44

Perter, the issue is that, Total_Invoice column contains an varchar data.. due to which OP is geting error..

Chirag

http://chirikworld.blogspot.com/

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-02-26 : 09:42:25

Thanks for that, but when I try using that with a non-numeric field then I get an error.

I assume that Min and Max should only be peformed on numeric values, but my audit script needs to run over all fields.

It can be the case that if the field is non-numeric then max value can be blank or set to 'N/A' or the actual Max value.


select Max(case when isnumeric(Gross_GBP) = 1 
				then abs(Gross_GBP) 
				else Gross_GBP 
			end) as [Max]
from MASTER_TABLE

Varchar data type:
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.
or
Money data type:
Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.
Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.
Msg 257, Level 16, State 51, Line 1
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-26 : 09:44:21

select Max(case when isnumeric(Gross_GBP) = 1
then cast(abs(Gross_GBP) as nvarchar)
else Gross_GBP
end) as [Max]
from MASTER_TABLE

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-26 : 09:48:06

Have you try using the isReallyNumeric() from the link posted by Harsh instead of the isnumeric() ?

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-02-26 : 09:56:28

Hi Peter, that works fine for varchar, but I still get the error when my values are of money datatype.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-26 : 10:14:53

I think you are wrong. You can't mix DATATYPES in same column.
They are all of the same datatype.
BUT... The data can represent different things.

declare @master_table table (gross_gbp nvarchar(25))

insert	@master_table
select	'56' union all
select	'xyz' union all
select	'-45.56' union all

select	'$100.10' union all
select	'$-200'


select	gross_gbp,
	isnumeric(gross_gbp)
from	@MASTER_TABLE

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-26 : 10:16:02

I am confuse. What is your table column's data type ? nvarchar or money ?

Please post your table structure with some sample data and the result that you want.

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-02-26 : 10:30:59

I require that script to run over all my fields in the table. Each field can have different datatypes. Therefore I need that script to cater for all datatypes and run the max check for each field.

If the field is non-numeric then it can pull out the max value or just a blank value. As I am only concerned mainly with the money/numeric fields values, but the script has to run on all fields....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-26 : 10:33:45

But you still want only one MAX for each column, right?

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-26 : 10:33:49

quote:
Originally posted by dnf999

I require that script to run over all my fields in the table. Each field can have different datatypes. Therefore I need that script to cater for all datatypes and run the max check for each field.

If the field is non-numeric then it can pull out the max value or just a blank value. As I am only concerned mainly with the money/numeric fields values, but the script has to run on all fields....

You should have mention this earlier.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-26 : 10:38:10

Must...restrain...my...fist...of...death...

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-26 : 10:52:48

quote:
Originally posted by dnf999

I require that script to run over all my fields in the table. Each field can have different datatypes. Therefore I need that script to cater for all datatypes and run the max check for each field.

If the field is non-numeric then it can pull out the max value or just a blank value. As I am only concerned mainly with the money/numeric fields values, but the script has to run on all fields....

Holy crap! Have you considered normalizing your data and using correct datatypes for your columns? It will make your life much, much easier.

- Jeff
http://weblogs.sqlteam.com/JeffS

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources