Forum Stats

  • 3,851,626 Users
  • 2,264,005 Discussions
  • 7,904,793 Comments

Discussions

Returning a default value in SQL when no rows found

2»

Comments

  • 105967
    105967 Member Posts: 1,027
    Yes, but I did not think of you for using it the wrong way :-)

    The problem lays in your sentence: "Of course a proper Index layout is assumed". If the OP thinks, he/she has now a general solution and he/she will try with any column of the table ...
  • Roger D
    Roger D Member Posts: 2 Blue Ribbon
    how about something of the form ...

    select c1, c2, c3 from tab where ...
    union
    select '*** empty' c1, null c2, null c3 from dual where not exists (select c1, c2, c3 from tab where ...)

    for example:
    /* when main query returns data */
    select dummy from dual
    union all
    select '*** empty' dummy from dual where not exists (select dummy from dual)
    ;

    /* when main query returns NO data */
    select dummy from dual where 1=2
    union all
    select '*** empty' dummy from dual where not exists (select dummy from dual where 1=2)
    ;
  • 663924
    663924 Member Posts: 1
    Set up your default message in a variable and then overwrite the default data when the query returns data. Try the following example...

    SQL> define msg="No data found"
    SQL> select '&&msg' from dual;

    'NODATAFOUND'
    -------------
    No data found

    SQL> col a new_value msg
    SQL> select 'X' a from dual where rownum < 1;

    no rows selected

    SQL> select '&&msg' from dual;

    'NODATAFOUND'
    -------------
    No data found

    SQL> select 'X' a from dual;

    A
    -
    X

    SQL> select '&&msg' from dual;

    '
    -
    X
  • Ganesh Srivatsav
    Ganesh Srivatsav Member Posts: 2,284 Silver Trophy
    you can use this,
    SQL> select ename from emp where empno=7902;
    
    ENAME
    ----------
    FORD
    
    SQL> SELECT ename
      2    FROM emp
      3   WHERE empno = 7902
      4  UNION ALL
      5  SELECT 'EMPTY'
      6    FROM DUAL
      7   WHERE NOT EXISTS ( SELECT ename
      8                       FROM emp
      9                      WHERE empno = 7902);
    
    ENAME
    ----------
    FORD
    
    SQL> SELECT ename
      2    FROM emp
      3   WHERE empno = 7909
      4  UNION ALL
      5  SELECT 'EMPTY'
      6    FROM DUAL
      7   WHERE NOT EXISTS ( SELECT ename
      8                       FROM emp
      9                      WHERE empno = 7909);
    
    ENAME
    ----------
    EMPTY
    
    SQL> 
    {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    ora_nut
  • 836203
    836203 Member Posts: 1
    SELECT NVL(<select that may return no data>, <default value>) FROM DUAL
  • 635471
    635471 Member Posts: 1,937
    cubmar wrote:
    Keith,

    There are two (very similar) examples of how to do it without the need for PL/SQL

    Thanks
    You usually try to use SQL rather than PL/SQL because it is more efficient to do so. In this case it is probably not.
  • BluShadow
    BluShadow Member, Moderator Posts: 42,307 Red Diamond
    user2310677 wrote:
    SELECT NVL(<select that may return no data>, <default value>) FROM DUAL
    Did you sign up to the forum, just to post that very basic answer to a question that's over 2 years old?
  • select nvl(to_char (c1),'no data') from (select case  (select 'present'

           from Table WHERE column = value ) when 'present'

           then (select column

           from table  WHERE column = value )

           else null 

           end as c1   from dual )

  • BluShadow
    BluShadow Member, Moderator Posts: 42,307 Red Diamond

    Another one!

    This thread is 5 years old!!!! FFS If the OP hasn't got an answer to their question yet, I'd be very concerned.

    Locking this thread

This discussion has been closed.