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

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

Guys,

I think part of the confusion was the use of the term "empty string".  
Evidently, Gina has informed me, in sql-server they have two different 
constructs - an empty string and a null.  I may be wrong, but I do not think 
this construct of the empty string exists anywhere but sql-server.  In 
Oracle the empty string '' (two ticks side by side) results in the value of 
null being inserted into the table.

If anyone else has any information about this, feel free to send it.

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----

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

-----Original Message-----
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-----


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.



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