Re: NVL doesn't short-circuit

From: <ctcgag_at_hotmail.com>
Date: 03 Nov 2003 21:23:02 GMT
Message-ID: <20031103162302.581$a5@newsreader.com>

Kenneth Koenraadt wrote:
> Hi Ctcgag,
>
> Cannot confirm your observation regarding DECODE (at least not in 9i):
>
> SQL>
> SQL> select decode('a','a',3,0/0) from dual;
>
> DECODE('A','A',3,0/0)
> ---------------------
> 3

How strange. I get the same thing as you. But using the sequence rather than the div by zero as an indicator, it definitely is evaluating the nextval:

SQL> select test_seq.nextval from dual;

   NEXTVAL

---

       137

SQL> select decode('a','a',3,test_seq.nextval) from dual;

DECODE('A','A',3,TEST_SEQ.NEXTVAL)

---

                                 3

SQL> / DECODE('A','A',3,TEST_SEQ.NEXTVAL)

---

                                 3

SQL> / DECODE('A','A',3,TEST_SEQ.NEXTVAL)

---

                                 3

SQL> / DECODE('A','A',3,TEST_SEQ.NEXTVAL)

---

                                 3

SQL> select test_seq.nextval from dual;

   NEXTVAL

---

       142

> PL/SQL *does* short-circuit.PL/SQL users guide states that :
>
> "Quote"
>
> "PL/SQL uses short-circuit evaluation. That is, PL/SQL stops
> evaluating the expression as soon as the result can be determined
> (...) In any case, it is a poor programming practice to rely on
> short-circuit evaluation.
>
> "Unquote"
>
> I do not agree with the last sentence, but that's personal.

I agree with your disagreement. Obviously it is poor practise to rely on it in this case, because it is unreliable. But where reliable, I'd say it is excellent practise.

Oh well, back to using PL/SQL loops rather than simple SQL to implement this. Too bad I don't get paid by the line.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB

Received on Mon Nov 03 2003 - 15:23:02 CST