Decode calculates before it evaluates

[steveisaloser]

Oracle say that decode performs the calculations before it determines which value it needs to calculate eg:<br><br>select (client_type, 'BAD', get.nasty_interest_rate,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'GOOD', get.nice_interest_rate)<br>into interest_to_pay<br>from dual;<br><br>Meaning the package function get.nasty_interest_rate and get.nice_interest_rate is calculated before it determines what the client_type contains.&nbsp;&nbsp;If you audit calculation accesses in get then you get lots of false hits and big overheads.&nbsp;&nbsp;This example is not the actual code but just illustrates the problem.&nbsp;&nbsp;I know that you would think you could use &quot;if then else&quot; but believe me in the case that I want to use this in, that would not be possible.&nbsp;&nbsp;My current work around is to use dynamic sql which does not impress me much.&nbsp;&nbsp;Anyone got any views on this problem.&nbsp;&nbsp;Is Oracle right, and why should this daft processing be like this?

 

[MikeLacey]

Steve,<br><br>Dumb question maybe but - why can't you use If.. Then.. etc in this context? <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[listener22]

&nbsp;&nbsp;&nbsp;&nbsp;I'm not sure that both get.interest_rate functions are evaluated.<br>Oracle does check that the functions exist and that you are allowed<br>to execute them.&nbsp;&nbsp;But in some very limited testing that I did, only<br>the 'selected' function was actually calculated.&nbsp;&nbsp;Maybe someone out there<br>has experience with this. <p>Jim Carlson<br><a href=mailto:nx56@inetarena.com>nx56@inetarena.com</a><br><a href= > </a><br>oracle, vb, some javascript