If I use a simple line like this

strSQL = "SELECT IsNumeric(Item_ID) FROM Management_Associations WHERE Management_ID = 1 AND Association_ID = " & Session("HOAID")

based on the documentation (albeit for an older version)

http://dcx.sap.com/1001/en/dbrfen10/rf-isnumeric-misc-functions.html

I would expect it to return either 0 or 1. However a 'no hit' just returns a blank not a zero.. How can I get this to work as documented?

Thanks

asked 12 Feb, 10:42

gchq's gravatar image

gchq
1814819
accept rate: 33%


In my tests (with v16.0.0.2344), isnumeric() does return 0 or 1, not null, even for null as argument.

drop table if exists Test;
create table Test (
   pk int not null primary key,
   col2 varchar(10) not null
);

insert Test values (1, 'Hi');
select isnumeric(pk), isnumeric(col2), isnumeric(null) from Test;
-- returns 1, 0, 0
select isnumeric(pk), isnumeric(col2), isnumeric(null) from Test where pk = 0;
-- returns an empty result set

Are you sure your result set is not simply empty (as in my second select)?

permanent link

answered 13 Feb, 02:20

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

I'm sure the result set would be empty as I'm testing to see if a record exists - I'm assuming (probably incorrectly) that an empty record set is the same as blank? As it happens setting the value of an integer to zero then running it against that query will maintain the value of zero with no hits so it still works

(13 Feb, 09:46) gchq
Replies hidden

No, these are (at least) 3 different concepts:

  • an "empty" value is a string of length 0 (i.e. '')
  • a "blank" is a particular character (space) or a string consisting of one space, so of length 1 (i.e. ' ')
  • a NULL value is a special value used to represent missing or inapplicable information
  • an empty result set means no row has fulfilled the conditions of your query. Note: It is a result set, not a value.


AFAIK, the COUNT() aggregate function is the only one to return a non-NULL value when applied to an empty result set, COUNT() will return 0 then.

(13 Feb, 10:03) Volker Barth

I guess that really COUNT() is what I should be using - Thank you.

(13 Feb, 14:26) gchq

I think the key words in the questions are 'no hit' meaning that no rows matched the search conditions and therefore the result set is empty.

When the query matches no rows then the value returned is NULL. This is normal and has always been the case. The behaviour that you are seeing has nothing to do with the isnumeric function.

An easy way to demonstrate this is:

begin
  declare @s long varchar;
  set @s = ( select table_id from systable where table_name = 'foobar' );
  select isnull( @s, '-null-' );
end;

Assuming you don't actually have a table named 'foobar' in your database, the above batch will show '-isnull-' when you execute it in dbisql[c]. Changing table_id to isnumeric( table_id ) in the above select query makes no difference to the result (returning '-isnull-') because no rows were selected so the isnumeric( table_id ) expression never gets evaluated at all.

HTH

permanent link

answered 13 Feb, 08:24

Mark%20Culp's gravatar image

Mark Culp
23.3k9132274
accept rate: 40%

edited 13 Feb, 08:25

When the query matches no rows then the value returned is NULL.

Is that generally true? I would think it depends whether the result is directly assigned to a SQL variable in contrast to access via cursors and the like.

I.e. when a cursor is used, it should return a SQLCODE 100 warning, as no rows are fetched at all, such as in this sample based on my Test table:

begin
   declare err_notfound
      exception for sqlstate '02000';
   declare bIsNumeric bit;
   declare crs cursor for select isnumeric(pk) from Test where pk = 0;
   open crs;
lpCrs:
   loop
      fetch next crs
         into bIsNumeric;
      if sqlstate = err_notfound then
         message 'No row found' to client;
         leave lpCrs;
      else
         select bIsNumeric;
      end if;
   end loop lpCrs; 
   close crs;
end;

I do not know what API is used by gchq, but often enough client APIs do tend to use cursors under the covers...

(13 Feb, 09:16) Volker Barth
Replies hidden

You are correct. My comment was made in the context of the example that I gave.

(13 Feb, 09:19) Mark Culp

According to the documentation

ISNUMERIC returns 1 when the input string evaluates to a valid integer or floating point number; otherwise it returns 0. The function also returns 0 if the string contains only blanks or is NULL.

It should always return a zero if the result is blank or null

(13 Feb, 09:41) gchq
Replies hidden
1

The documentation is correct. As I said in my answer, the issue in your example (as I understand it) is that there are no rows returned by your query and therefore the query does not even get to the stage of evaluating the isnumeric expression - i.e. it is not executed - because there is no row, hence no value, in which to evaluate! In these circumstances the returned value (from a subquery evaluation) will be NULL.

(13 Feb, 09:47) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90

question asked: 12 Feb, 10:42

question was seen: 214 times

last updated: 13 Feb, 14:26