return a default row in sql

Question

Is it possible in oracle sql to return a default row if no rows found. I have a process where in rows fetched will be put in a flat ascii file. now i have a requirement that if there are no rows fetched by the sql query then there should be a default row in the ascii file.

is it possible in sql to output a default row if no rows fetched by the query note: i dont want to use pl/sql.

Answer 1

You could use UNION ALL for this:

select a, b, c from foobar 
where foo='FOO'
union all
select 'def', 'ault', 'value' from dual 
where not exists ( select 'x' from foobar where foo='FOO' )

Answer 2

I suspect that it would be cleaner to have the process that the writes the ASCII file write the default data if no rows are returned rather than getting Oracle to do it. If the query that you're using is expensive, you'd significantly increase that cost if you fudge it to return a default row as ammoQ and David Oniell have done.

Answer 3

For complex queries where the overhead on finding out if there is a row in the result set is onerous or the query is just very large and unwieldy, then a subquery factoring clause might be beneficial:

With my_query as
    (
    select a, b, c from foobar where foo='FOO' 
    )
Select a,b,c
From   my_query
Union All
Select ...
From   dual
Where  Not Exists
       (Select 1 from my_query)
/