Is it advisable to call Function directly in Case statement?

If the function is not deterministic and it is called several times with the very same parameters, then it's best to store the return value in a variable in order to avoid computing again and again the same thing.

On of the most monumental piece of code I encountered which shows how not to use functions was something like below:

if f(x,y,z)=1 then

...

elsif f(x,y,z)=2 then

...

else

...

end if;

So, the same function was computed twice...

And that piece of code was in Oracle Forms and the function, as far as I recall, was also inside the form and contained some SQL, so that there was also the roundtrip to the database...

Still, if in a case a function is called with various parameters (not with the same parameters several times!), then there is no performance improvement if you store that value in some variable and you just have to bother with declaring that variable which you will not use further on and will just take some memory.

I don't see an issue, and with my temperament (which is not necessarily the same as anyone else's) I know I would push hard against this requirement. To me it makes no sense.

A variable to store the return value of the function would make sense if you used the value more than once in your code. You do not.

Someone will say "but what if you need to change the code in the future, you will be glad you made up a variable and used it." My view on that is "we'll cross that bridge when we get there." No one can guess correctly all the changes the code will have to go through in the future, and trying to anticipate all such hypothetical events is a fool's errand. And the amount of work is the same whether you do it now or at that time. I don't see how doing it now, in anticipation of some speculative change in the future, will save time overall for anyone.

I do, on the other hand, see the merit of writing the code exactly as you did. If you created a variable in a declarations section, then assigned a value to it at the top of your execution block, and then used the variable 150 lines of code later, you would have to do a search for the variable name to remember what it stood for. Even more so for someone who didn't write the code, but will be tasked to maintain it in the future. No such complication when you call the function directly where you use its return value - the function call is right there for all to see.

Of course, in many cases people will not push back; it takes less effort to just do as you are told and move on to something else. In my opinion that's a shame; that's how we end up with all sorts of well-established but nonsensical customs, rules and policies.

I'd agree with everything that mathguy wrote.

Couple of extra points, though:

a) While the response might be appropriate to your simplified code, there might be sufficient extra complexity in the production code to make people answer differently.

b) you could even write the code fragment in a way that makes the "got to create a variable" argument even weaker:

begin

        dbms_output.put_line(

                case when calculate_nu (5,7) > 10

                        then 'Number is greater than 10'

                else

                        'Number is less than 10'

                end

        );

end;

/

c) mathguy's argument about "the variable declaration could be a long way from the usage" can be answered easily because you can declare an entire pl/sql block pretty much wherever you want, so the following could be embedded almost anywhere in the code (this doesn't mean I'd want to do it, but if I had to enlarge a fragment from (b) above top allow for multiple cases that's the way I might do it):

declare

        m_dummy number := calculate_nu (5,7);

begin

        dbms_output.put_line(

                case when m_dummy > 10

                        then 'Number is greater than 10'

                else

                        'Number is less than 10'

                end

        );

end;

/

It's probably worth pointing out that for the specific "two options only " case of (c) the PL/SQL optimizer might be smart enough to optimize out the declaration and effectively turn the compiled code into the (b) form that your supervisor wasn't keen on.

Regards

Jonathan Lewis