plsql confusion 3

[voodoojon]

the if statements in my code appear not to be working

dbms_output.put_line('other = '||greportinfo.Other);
If not greportinfo.Other = '' then
 vsql:= vsql||'where portfoliotype = '||greportinfo.Other ;
 dbms_output.put_line('first if');
 if not greportinfo.Owner = '' then
  vsql:= vsql||' and nominee = '|| greportinfo.Owner; 
 end if;
else
 dbms_output.put_line('other = '||greportinfo.Other);
 dbms_output.put_line('first else');
 if not greportinfo.Owner = '' then
  vsql:= vsql||'where nominee = '||greportinfo.Owner ;
 end if;
end if;

when run through, this prints

other = DEAD
other = DEAD
first else

to the dbms output window

so even though greportinfo.other = 'DEAD',

(not greportinfo.other = '') is evaluated as false when it is clearly true.

what am I doing wrong?


Jon

One day I will find a signature worthy of this space. That day has not yet come.

 

[BJCooperIT]

Instead of

If not greportinfo.Other = ''

try:

If greportinfo.Other is not null

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[SantaMufasa]

Jon,

You has stumbled upon the "joys and beauties" of Oracle NULL evaluation...your comparison string ('') means the same as the Oracle function, NULL.

Oracle compares NULL ('') in a very important way. First, any expression that contains NULL actually contains no bits (not spaces, not 0, not anything). When you use the "=" operator in Oracle (and most other environments) the operator means, "look at the BITs in the operand to the left of the "=" and look at the BITs in the operand to the right of the "=" and compare them...If the BIT configurations match, then the result of the "=" comparison is TRUE.

NULL contains no bits. To then best understand how Oracle treats NULL when comparing, let's give NULL yet another synonym: "An UNKNOWN VALUE".

Outcome 1: Given the boolean expression, "IF 'A' = ''", Oracle asks, "Are the ASCII BITs that represent the character, 'A', equal to an UNKNOWN VALUE?" The answer is certainly not TRUE; the answer is not FALSE; the answer, itself, is an UNKNOWN VALUE (i.e., NULL). But since Oracle follows the logic path for TRUE only when the answer evaluates to TRUE, then Oracle has no choice but to follow the logic path for "not TRUE" (meaning FALSE or NULL).

Outcome 2: Given the boolean expression, "IF NULL = ''",
Oracle asks, "Is an UNKNOWN VALUE equal to an UNKNOWN VALUE?" The answer is certainly not TRUE; the answer is not FALSE; the answer, again, is an UNKNOWN VALUE (i.e., NULL). Again, has no choice but to follow the logic path for "not TRUE" (meaning FALSE or NULL).

The only way to obtain a TRUE response when comparing with an expression with an UNKNOWN VALUE is to ask, effectively, "Is an UNKNOWN VALUE an UNKNOWN VALUE?" That answer is TRUE. The Oracle syntax for asking that conditional question is:

{WHERE | IF} <expression> [b]IS NULL[/b] <TRUE activity>

So your code can/should alternately read:

dbms_output.put_line('other = '||greportinfo.Other);
If greportinfo.Other [b]is NOT NULL[/b] then
 vsql:= vsql||'where portfoliotype = '||greportinfo.Other ;
 dbms_output.put_line('first if');
 if greportinfo.Owner [b]IS NOT NULL[/b] then
  vsql:= vsql||' and nominee = '|| greportinfo.Owner; 
 end if;
else
 dbms_output.put_line('other = '||greportinfo.Other);
 dbms_output.put_line('first else');
 if greportinfo.Owner [b]IS NOT NULL[/b] then
  vsql:= vsql||'where nominee = '||greportinfo.Owner ;
 end if;
end if;

Let us know if this produces the results for which you are looking.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

Sorry for the late, duplicate reply, Barb. While composing a response, I received several interruptive phone calls, thus my delay. Good, correct answer.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[voodoojon]

cheers BJCooper, that sorted it

Jon

One day I will find a signature worthy of this space. That day has not yet come.

 

[voodoojon]

Mufasa, your explanation was useful too

Jon

One day I will find a signature worthy of this space. That day has not yet come.

 

[SantaMufasa]

Thanks, Jon, but my saying "You has..." instead of "You have..." in my reply means you should revoke my Purple Star.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[BJCooperIT]

The advantage of the short & sweet answers is fewer interruptions. However, with explantions like yours, I will take a few typos anyday!

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[Turkbear]

Hi,
I agree, Barb..Santa's details help demystify Oracle's almost MetaPhysical use of NULL...






To Paraphrase:"The Help you get is proportional to the Help you give.."