Re: Use default value when no rows returns.

Beto <andredigi_at_hotmail.com> wa:
>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

This should work assuming you really are only selecting one column in the case where rows are actually returned. However, it does include a UNION and a subquery.

SELECT value FROM tableA WHERE date > (...)   UNION
SELECT '-1' FROM dual WHERE (SELECT count(*) FROM tableA WHERE date > (...))=0;

It may be more efficient to catch the lack of rows returned in the shell script somehow rather than trying to do it from the select, in this case.

-- 
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators

"Keep your fingers off the lens." --Elton Byington, English Translator