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

["White, Denise" <DEWhite@vicr.com>]

Hi Gina,

To add to what Steve has said, the only data types that can be null in an 
Oracle PeopleSoft database are dates (& times) and long character fields.  
However, you are on SQL Server, and I'm not sure if that allows nulls in 
character fields.

I am not exactly sure what the definition of 'empty string' is, but in your 
third example, you seem to be showing a null value.  Perhaps when the message 
was posted, a space somehow was stripped out, but when I put my cursor in the 
string, there is no space between the quotes.  That is a null value, and would 
be treated as such by the isnull() function.  If there was a space there, then 
I agree that isnull() should not be true, although isblank() would.

Denise M. White
Sr. Software Engineer
HR/Payroll Systems
Vicor Corporation
978-749-3281
dewhite@vicr.com
------------------------------

Message: 12
Date: Thu, 8 Jul 2004 15:29:15 -0700 
From: "Alexander, Steve" <Steven.Alexander@sanjoseca.gov>
Subject: RE: [sqr-users] Using the LET functions Isnull or Nvl with
        CHAR orV        ARCH AR database columns 

I can't answer all your questions, but there is one aspect of the Peoplesoft
environment you should consider.  When Peoplesoft creates a table (at least
in Oracle), it sets the "null not allowed" flag.  Character fields cannot be
null; the "least" they can be is a space.  Numeric fields cannot be null;
the "least" they can be is 0.

-----Original Message-----
From: Bencke, Gina [mailto:GinaBencke@forestcity.net]
Sent: Thursday, July 08, 2004 2:58 PM
Subject: [sqr-users] Using the LET functions Isnull or Nvl with CHAR
orVARCH AR database columns 


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