RE: [sqr-users] Using the LET functions Isnull or NvlwithCHARorVARCHAR database columns

["Turner, Ivan" <Ivan.Turner@qwest.com>]

isblank() handles null, blank and white space.  Consider its use
instead.

-----Original Message-----
From: sqr-users-bounces+ivan.turner=qwest.com@sqrug.org
[mailto:sqr-users-bounces+ivan.turner=qwest.com@sqrug.org] On Behalf Of
Alexander, Steve
Sent: Friday, July 09, 2004 11:31 AM
To: 'This list is for discussion about the SQR database reportinglangu
age from Hyperion Solutions.'
Subject: RE: [sqr-users] Using the LET functions Isnull or Nvl
withCHARorVARCHAR database columns


I don't think there is a difference between null and "the empty string,"
which I always called "the null string."  Also, I think the isnull()
function does return 1 or 0 - try using it in an arithmetic formula.

-----Original Message-----
From: Larry Roux [mailto:LRoux@syr.edu]
Sent: Friday, July 09, 2004 8:13 AM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Using the LET functions Isnull or Nvl
withCHARorVAR CHAR database columns


isnull returns true or false - which SHOULD NEVER be confused with 0 or
1 (even though most software codes it that way).

Why not just do:

if  isnull(&ENTITY)
   Let $line3 = '00'
else
   Let $line3 = '01'
end-if

or better yet:

if &ENTITY = ''
   Let $line3 = '00'
else
   Let $line3 = '01'
end-if

we usually check for an empty string or null this way:

if RTRIM(&ENTITY,' ') = ''
   ...
end-if




Larry Roux
Syracuse University
lroux@syr.edu


>>> GinaBencke@forestcity.net 07/09/04 10:55AM >>>
The problem I was running into was not only the ISNULL function, but in
the use of EDIT(ISNULL(&ENTITY), '09).  Once I made the following
changes my results were more acceptable.  However I still determined
that SQR can not truly distinguish between a column containing an empty
string and one containing a NULL value.  Obviously I can get around this
using the database function ISNULL instead of SQR's function so I shall
not dwell on this.  

However, can anyone explain why the below sections of code produce
different output???

Originally my code which I thought was printing the appropriate ISNULL
results was as follows:

let $line3 = 'isnull(&ENTITY) expected: 01            actual: <' ||
edit(isnull(&ENTITY), '09') || '>'
print $line3 (+1,1)
Result  "isnull(&ENTITY) expected: 01            actual: <00>"

After receiving 'the dragons' email I suspect the edit function was not
working as expected and changed it to: let #null = isnull(&ENTITY)
let $line3 = 'isnull(&ENTITY) expected: 01            actual: <' ||
edit(#null, '09') || '>'
print $line3 (+1,1)
Result "isnull(&ENTITY) expected: 01            actual: <01>"


Btw - We are using some tables that are not created in PeopleSoft and
thus may contain NULL values.


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


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

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

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