[sqr-users] Using the LET functions Isnull or Nvl with CHAR orVARCHAR database columns

["Bencke, Gina" <GinaBencke@forestcity.net>]

Lets assume that I have a need to distinguish between a NULL column
value and a column containing an empty string. Has anyone had success
using ISNULL or NVL LET functions with CHAR or VARCHAR database column
variables.   Are these functions truly designed to distinguish between
an undefined column variable, a column variable with a NULL value, or an
empty string? It seems that SQR treats all three of these identically.

It appears as if isnull(&col) always returns 0 whether &col contains a
value or not :
  the database column is not defined (i.e. the select did not return a
row)   -- 1 is what I would expect  or
  the database column is NULL  (i.e. select col from table where col  IS
NULL) -- 1 is what I would expect or
  the database column is an empty string (i.e. select '' )  --  0 is
what I would expect                   or
  the database column has a value (i.e. select 'a' ) --  0 is what I
would expect 

It appears the nvl(&col, 'NULL') returns 'NULL' regardless of whether
the select fails to return a row, the database column is an empty
string, or the database column contains a NULL value:
  the database column is not defined (i.e. the select did not return a
row)  -- 'NULL' is what I would expect     or
  the database column is NULL  (i.e. select col from table where col  IS
NULL)  -- 'NULL' is what I would expect  or
  the database column is an empty string (i.e. select '' ) -- I would
expect &col or ''  to be returned

Granted, my examples are simplistic, but the behavior holds true even
when the samples are not so.

I am running in the following environment:
  SQL SERVER 8.00.194 (ODBC Sql Server 2000.85.1025.00)
  SQR for PeopleSoft 8.44.07


Gina Bencke
Bencke Consulting Corporation

Office (440) 519-0060
email: gina.work@bencke.com


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users