Re: SQL Problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 12 Aug 1999 13:47:27 GMT
Message-ID: <37b3cf46.68411981@newshost.us.oracle.com>

A copy of this was sent to "Mike Heisz" <mheisz_at_rim.net> (if that email address didn't require changing) On Thu, 12 Aug 1999 09:11:03 -0400, you wrote:

>I am trying to write a query to return 1 value.
>Sometimes there is no value and the query returns no data. i.e
>select a
>from b
>where
>...... (here the conditions ensure that 1 or 0 rows are returned by the
>query)
>

if you are 100% positive that your where clause gaurantees that 0 or 1 rows are returned then:

select max(c1) c1, max(c2) c2, max(c3) c3, ....   into ....
  from b
 where <where clause>

will always return a record. This shows what would be returned in various cases:

SQL> select max(dummy) from dual where 1=0; M
-

1 row selected.

that returns NULL since there is no max. Note that it does return a row.

SQL> select max(dummy) from dual where 1=1;

M
-
X

1 row selected.

that returns the value as expected.

SQL> select nvl(max(dummy),'no data') from dual where 1=0;

NVL(MAX

---

no data

1 row selected.

that shows how to 'default' the value to something you want when there is no matching value.

>I need to supply a defeault value to be used when there are 0 rows returned.
>Is this possble and if so how do you do it?
>
>A couple of notes: This is part of a PL/SQL procedure but I can't use the
>NO_DATA_FOUND exception handler because I am dealing with files and this is
>used there.
>

I don't understand the connection between 'files' and not being able to use an exception handler. You can ALWAYS code:

   ... some code
   ...
   begin

      select a 
        into c
        from b 
       where <where clause>;
   exception
       when no_data_found then
          c := some_value;

   end;
   .....
   ..... some more code

anywhere in plsql.....

>Thanks,
>
>Mike
>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Aug 12 1999 - 08:47:27 CDT