SAP SQL Anywhere

Question

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

Accepted Answer

2

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 '17, 02:20

Volker Barth
40.2k●361●550●822
accept rate: 34%

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 '17, 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 '17, 10:03) Volker Barth

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

(13 Feb '17, 14:26) gchq

flat view

Answer 2

2

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 '17, 08:24

Mark Culp
24.9k●10●141●297
accept rate: 41%

edited 13 Feb '17, 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 '17, 09:16) Volker Barth

Replies hidden

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

(13 Feb '17, 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 '17, 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 '17, 09:47) Mark Culp

flat view

SAP SQL Anywhere

IsNumeric returning blank instead of zero

Follow this question

Related questions