sqloracle-databaseora-01403

Oracle: Get a query to always return exactly one row, even when there's no data to be found


I have a query like this:

   select data_name
   into v_name
   from data_table
   where data_table.type = v_t_id

Normally, this query should return exactly one row. When there's no match on v_t_id, the program fails with a "No data found" exception.

I know I could handle this in PL/SQL, but I was wondering if there's a way to do this only in a query. As a test, I've tried:

select case
           when subq.data_name is null then
            'UNKNOWN'
           else
            subq.data_name
       end
from (select data_name
       from data_table
       where data_table.type = '53' /*53 does not exist, will result in 0 rows. Need fix this...*/
       ) subq;

...but this will obviously not work (because subq being empty is not the same as subq.data_name is null). Is this even possible or should I just check in my PL/SQL solution?

(oracle 10g)


Solution

  • There's ways to make this simpler and cleaner, but this basically spells out the technique:

    SELECT data_name
    FROM data_table
    WHERE data_table.type = v_t_id
    
    UNION ALL
    
    SELECT NULL AS data_name
    FROM dual
    WHERE NOT EXISTS (
        SELECT data_name
        FROM data_table
        WHERE data_table.type = v_t_id
    )
    

    When the first part of the union is empty the second will contain a row, when the first part is not empty, the second will contain no rows.

    If the query is takes to much time, use this one:

    SELECT * FROM (  
        SELECT data_name
        FROM data_table
        WHERE data_table.type = v_t_id
    
        UNION ALL
    
        SELECT NULL AS data_name
        FROM dual
      ) WHERE data_name is not null or ROWNUM = 1