Can I do better than this query?

 select func_returns_str('key1') || 
        nvl2( func_returns_str('key2'), 
              ',' || func_returns_str('key2'), 
   from dual;

I would like to avoid calling func_returns_str('key2') twice. func_returns_str returns a varchar.

Elaborating further: func_returns_str could be a generic function like substr() in Oracle which you can't change the definition.

  • 1
    Can you provide any more information about the table? What kind of results you are looking for? What does func_returns_str do besides return a varchar (lookup, etc)? – SqlSandwiches Jun 22 '11 at 1:08
  • With a built in functions, Oracle may know which ones are idempotent and just re-use the return value instead of calling twice, without you needing to change your code. SQL is declarative, not imperative. Not leaving as an answer because I'm unsure how to prove either way. – Shannon Severance Aug 8 '11 at 22:16

You can create a wrapper for the nvl2 call:

create or replace function add_comma(s varchar) return varchar as
  return case when s is null then null else ','||s end;

with t as (select 'key2' as val from dual union all select null from dual)
select val, nvl2( substr(val, 3, 2), ','||substr(val, 3, 2), null) as orig, 
       add_comma(substr(val, 3, 2)) as wrapped
from t;

---- ---- -------
key2 ,y2  ,y2    

alternatively you can use a with clause (also know as a CTE ):

with t as (select substr('key2', 3, 2) as s from dual)
select nvl2(s, ','||s, null) from t;
| improve this answer | |

You could create a function to wrap func_returns_str in that could be used for the later calls and appends the comma itself if the return value is not null.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.