tag:blogger.com,1999:blog-7849367040589270673.post5927712693101603934..comments2024-03-21T22:50:39.997-07:00Comments on Obsessed with Oracle PL/SQL: Watch out for redundant code with WHILE loopsSteven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-7849367040589270673.post-73179174320163161302016-08-03T07:55:14.222-07:002016-08-03T07:55:14.222-07:00*Sigh* of course you're right... my output is ...*Sigh* of course you're right... my output is the same as yours. I fell into my own trap while trying to be smart.<br /><br />I only used an if/then/else, like this:<br /><br /> if (null and true) then<br /> dbms_output.put_line('true'); <br /> else<br /> dbms_output.put_line('false'); <br /> end if;<br /><br />This outputs 'false' when the actual value is null.<br />Well... it shows how difficult it is to evaluate null conditions ;)Ropnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-61983990314665772312016-08-03T07:30:30.381-07:002016-08-03T07:30:30.381-07:00@Rop, thanks for writing, but I need some clarific...@Rop, thanks for writing, but I need some clarification. When I execute this block:<br /><br />DECLARE<br /> PROCEDURE bplstr (str IN VARCHAR2, val IN BOOLEAN)<br /> IS<br /> BEGIN<br /> DBMS_OUTPUT.put_line (<br /> str<br /> || ' - '<br /> || CASE val<br /> WHEN TRUE THEN 'TRUE'<br /> WHEN FALSE THEN 'FALSE'<br /> ELSE 'NULL'<br /> END);<br /> END bplstr;<br />BEGIN<br /> bplstr ('(NULL AND FALSE)', (NULL AND FALSE));<br /> bplstr ('(NULL AND TRUE)', (NULL AND TRUE));<br /> bplstr ('(NULL OR FALSE)', (NULL OR FALSE));<br /> bplstr ('(NULL OR TRUE)', (NULL OR TRUE));<br />END;<br />/<br /><br />I see:<br /><br />(NULL AND FALSE) - FALSE<br />(NULL AND TRUE) - NULL<br />(NULL OR FALSE) - NULL<br />(NULL OR TRUE) - TRUE<br /><br />What do you see?Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-89139076094324842882016-08-03T05:47:44.036-07:002016-08-03T05:47:44.036-07:00Great tip Steven!
There is a subtle difference be...Great tip Steven!<br /><br />There is a subtle difference between the two versions though... In your second example, l_overdue_count = NULL in the first iteration. It requires that one knows how nulls are handled in calculations and boolean evaluations, e.g:<br /><br />(null <= 0) evaluates to null<br />(null or a) evaluates to a<br /><br />But in the case of AND<br />(null and false) = false<br />(null and true) = false<br />This last one can surprise you if you're not paying attention ;)<br />Ropnoreply@blogger.com