Isnumeric and Null Values - 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

General SQL Server Forums

New to SQL Server Programming

Isnumeric and Null Values

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-21 : 16:45:58

I tried googling this but got different results.

If I use an isnumeric function and the value of the field is null, what should be returned? isnumeric(null) = ?

I am working in SQL 2000.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-21 : 16:49:29

DECLARE @Sample INT,
	@s VARCHAR(200)

SELECT	ISNUMERIC(@Sample),
	ISNUMERIC(@s)

E 12°55'05.63"
N 56°04'39.26"

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-21 : 17:04:25

I thought so, but what am I doing wrong?

SELECT fpartno, frev, sum (YourValue) AS InvAdded
  FROM (SELECT poitem.fpartno,
               poitem.frev,
               CASE
                  WHEN (isnumeric (ext.[SALESORDERNO]) = 0
                     OR ext.[SALESORDERNO] = 0)
                  THEN
                     0
                  ELSE
                     (poitem.fucostonly * (poitem.fordqty - poitem.frcpqty))
               END
                  AS YourValue
          FROM       M2MDATA01.dbo.pomast pomast
                  INNER JOIN
                     M2MDATA01.dbo.poitem poitem
                  ON pomast.fpono = poitem.fpono
               LEFT OUTER JOIN
                  M2MDATA01.dbo.poitem_ext ext
               ON ext.[FKey_ID] = poitem.[identity_column]
         WHERE (poitem.frelsno <> '  0' OR poitem.fmultirls <> 'Y')
           AND pomast.fstatus = 'OPEN'
           AND poitem.FLSTPDATE <= '01/01/2009 12:00:00 AM'
           AND poitem.[fcategory] = 'INV') t
GROUP BY fpartno, frev

The value returned from this query is much too high as if I'm including way too many records. Ext.SalesorderNo will have 4 types of values. A null, 0 (zero), 'STOCK', and a 6 digit integer.

I thought my Case When would pull out all the nulls, zeros, and text values and assign the InvAdded a 0, and for those records with a valid 6 digit number it would calculate the $ added to inventory.

This isn't working out but I cannot understand why. Anybody?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-21 : 17:17:41

[code]SELECT fpartno,
frev,
sum(YourValue) AS InvAdded
FROM (
SELECT poitem.fpartno,
poitem.frev,
CASE
WHEN ext.[SALESORDERNO] LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' THEN poitem.fucostonly * (poitem.fordqty - poitem.frcpqty)
ELSE 0
END AS YourValue
FROM M2MDATA01.dbo.pomast as pomast
INNER JOIN M2MDATA01.dbo.poitem as poitem ON pomast.fpono = poitem.fpono
LEFT JOIN M2MDATA01.dbo.poitem_ext as ext ON ext.[FKey_ID] = poitem.[identity_column]
WHERE (poitem.frelsno <> ' 0' OR poitem.fmultirls <> 'Y')
AND pomast.fstatus = 'OPEN'
AND poitem.FLSTPDATE <= '01/01/2009 12:00:00 AM'
AND poitem.[fcategory] = 'INV'
) as t
GROUP BY fpartno,
frev[/code]

E 12°55'05.63"
N 56°04'39.26"

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-21 : 17:24:33

Actually, it's the opposite, but that's wonderful Peso. Thanks.

SELECT		fpartno,
		frev,
		sum(YourValue) AS InvAdded
FROM		(
			SELECT		poitem.fpartno,
					poitem.frev,
					CASE
						WHEN ext.[SALESORDERNO] LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' THEN 0
						ELSE poitem.fucostonly * (poitem.fordqty - poitem.frcpqty)
					END AS YourValue
			FROM		M2MDATA01.dbo.pomast as pomast
			INNER JOIN	M2MDATA01.dbo.poitem as poitem ON pomast.fpono = poitem.fpono
			LEFT JOIN	M2MDATA01.dbo.poitem_ext as ext ON ext.[FKey_ID] = poitem.[identity_column]
			WHERE		(poitem.frelsno <> '  0' OR poitem.fmultirls <> 'Y')
					AND pomast.fstatus = 'OPEN'
					AND poitem.FLSTPDATE <= '01/01/2009 12:00:00 AM'
					AND poitem.[fcategory] = 'INV'
		) as t
GROUP BY	fpartno,
		frev

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 00:00:12

handle ISNUMERIC() always with care. Try the below and see why its not always reliable

SELECT ISNUMERIC('1d2'),ISNUMERIC('1e2')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-22 : 03:22:57

quote:
Originally posted by visakh16

handle ISNUMERIC() always with care. Try the below and see why its not always reliable

SELECT ISNUMERIC('1d2'),ISNUMERIC('1e2')

Also


select 
	isnumeric('$'),
	isnumeric(','),
	isnumeric('.')

Madhivanan

Failing to plan is Planning to fail

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-22 : 09:17:13

quote:
Originally posted by visakh16

handle ISNUMERIC() always with care. Try the below and see why its not always reliable

SELECT ISNUMERIC('1d2'),ISNUMERIC('1e2')

Alright, is there a better way to achieve what I have up there?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 09:53:07

quote:
Originally posted by DavidChel

quote:
Originally posted by visakh16

handle ISNUMERIC() always with care. Try the below and see why its not always reliable

SELECT ISNUMERIC('1d2'),ISNUMERIC('1e2')

Alright, is there a better way to achieve what I have up there?

what are you trying to validate?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-22 : 10:12:52

well,, in some way 1e2 and 1d2 is numeric.. If you are importing from Excel that's what you are going to get unfortunately.
So, like visa said, depends on what you are going to get out of it....

1e2 = 100 (can be converted to float)
$ = 0 (money type)
, = 0 (money type)
. = 0 (money type)

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-22 : 10:17:54

I'm sorry guys, you're right, I don't have the complete set up in this thread. It's related to this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112985

This is what the data should look like.

fpartno   frev  
Widget    000   300,000
Dohickey  001   250,000

And, as I said before:

quote:
Ext.SalesorderNo will have 4 types of values. A null, 0 (zero), 'STOCK', and a 6 digit integer.

Therefore, I used the last query with isnumeric to make the records with value 0, null, or non numeric characters = 0 and calculated the rest accordingly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 11:15:35

quote:
Originally posted by DavidChel

I'm sorry guys, you're right, I don't have the complete set up in this thread. It's related to this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112985

This is what the data should look like.
fpartno frev
Widget 000 300,000
Dohickey 001 250,000

And, as I said before:

quote:
Ext.SalesorderNo will have 4 types of values. A null, 0 (zero), 'STOCK', and a 6 digit integer.

Therefore, I used the last query with isnumeric to make the records with value 0, null, or non numeric characters = 0 and calculated the rest accordingly.