Re: Use default value when no rows returns.

On Oct 23, 5:55 am, Beto <andred..._at_hotmail.com> wrote:
> Hi All,
>
> I need a help.
>
> I have a query inside one shell script and i'm wondering how to return
> any default value when no rows return from my query.
>
> For example:
>
> printf "%s\n" "set pages 0" "set feed off"
> "SELECT value FROM tableA WHERE date > to_date('01/11/2010','dd/mm/
> yyyy') ;"
> | sqlplus -s user/password | while read nr3
>
> from my query "SELECT value FROM tableA WHERE date >
> to_date('01/11/2010','dd/mm/yyyy') ;" no value is returned and when no
> row return i would like to assign a default value like '-1 ' for
> example.
>
> value
> --------
>  -1
>
> Thanks in advance!!

The only solution I can think of is some aggregate function. For example
select nvl(max(value),-1) FROM tableA
 WHERE date > to_date('01/11/2010','dd/mm/yyyy') ; guarantees one row. Would using some aggregate function work for you?

Ed Received on Thu Oct 23 2008 - 08:18:09 CDT