6 Replies Latest reply: Nov 14, 2012 6:37 AM by Solomon Yakobson RSS

    create simple function

    user8660054
      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
          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
            Query you posted:
            select  max(tran)
              from  p6.trans
            /
            Always returns one row.

            SY.
            • 3. Re: create simple function
              Solomon Yakobson
              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
                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
                  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
                    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;