Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can I do better than this query?

 select func_returns_str('key1') || 
        nvl2( func_returns_str('key2'), 
              ',' || func_returns_str('key2'), 
              null) 
   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.

share|improve this question
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

2 Answers 2

You can create a wrapper for the nvl2 call:

create or replace function add_comma(s varchar) return varchar as
begin
  return case when s is null then null else ','||s end;
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;

VAL  ORIG WRAPPED
---- ---- -------
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;
share|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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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