This discussion is archived
6 Replies Latest reply: Nov 14, 2012 4:37 AM by Solomon Yakobson RSS

create simple function

user8660054 Newbie
Currently Being Moderated
Hi all,

how to create a simple function which returns value in one row and if there is no result returns output ("no data found").

Select statement:
select max(tran)
from p6.trans

I am quite new to pl/sql therefore any help would be appreciated.
  • 1. Re: create simple function
    jeneesh Guru
    Currently Being Moderated
    You dont need a function
    select nvl(max(tran),'no data found'))
    from p6.trans;
    
    "If tran is a number"
    
    select nvl(to_char(max(tran)),'no data found'))
    from p6.trans;
    
    "To add, if you expect NULL for tran
    
    select decode(count(*),0,'no data found',to_char(max(tran)))
    from p6.trans;
    
    
    {code}
    
    Edited by: jeneesh on Nov 14, 2012 6:04 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 2. Re: create simple function
    Solomon Yakobson Guru
    Currently Being Moderated
    Query you posted:
    select  max(tran)
      from  p6.trans
    /
    Always returns one row.

    SY.
  • 3. Re: create simple function
    Solomon Yakobson Guru
    Currently Being Moderated
    jeneesh wrote:
    select nvl(max(tran),<font color=red>'no data found'</font>))
    ??? You can't make such conslusion unless column tran is NOT NULL.

    This will:
    select  case count(*)
              when 0 then 'no data found'
              else to_char(max(tran))
            end
      from p6.trans
    /
    SY.

    Edited by: Solomon Yakobson on Nov 14, 2012 7:35 AM
  • 4. Re: create simple function
    jeneesh Guru
    Currently Being Moderated
    Solomon Yakobson wrote:
    jeneesh wrote:
    select nvl(max(tran),<font color=red>'no data found'</font>))
    ??? You can't make such conslusion unless column tran is NOT NULL.

    SY.
    Umm, I recalled it just before you say, edited my post also..
  • 5. Re: create simple function
    user8660054 Newbie
    Currently Being Moderated
    Thank you,

    I need a procedure or a function which will be executed automatically with unix shell scripts. Just a some kind a block?!

    Regards,
  • 6. Re: create simple function
    jeneesh Guru
    Currently Being Moderated
    Like..
    create function foo return varchar2 is
     lc_out varchar2(50);
    begin
     select decode(count(*),0,'no data found',to_char(max(tran)))
     into lc_out
     from p6.trans;
     return lc_out;
    end;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points