RE: [sqr-users] Using the LET functions Isnull or Nvl with CHARorVARCHAR database columns

["the dragon" <ceprn@hotmail.com>]

Gina,

I am not certain whether I understand what it is you're trying to do or not, 
but you can always use

if isnull(&column)
  show 'this value is null'
else
  if isblank(&column)
    show 'this value is blank'
  else
    show 'this column contains value : ' &column
  end-if
end-if

hope this helps.

peace,
clark 'the dragon' willis


PSA: Salary <> Slavery.  If you earn a salary, your employer is renting your 
services for 40 hours a week, not purchasing your soul.  Your time is the 
only real finite asset that you have, and once used it can never be 
recovered, so don't waste it by giving it away.

"Time is the coin of your life. It is the only coin you have, and only you 
can determine how it will be spent.  Be careful lest you let other people 
spend it for you."

Carl Sandburg
(1878 - 1967)

----Original Message Follows----

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