Forum Stats

  • 3,851,765 Users
  • 2,264,024 Discussions
  • 7,904,843 Comments

Discussions

Categories

Returning a default value in SQL when no rows found

cubmar
cubmar Member Posts: 89
edited Oct 4, 2013 8:34AM in SQL & PL/SQL
Hi

How can change my SQL to return a default value if no rows are found?

Eg:
select text from text_file where criteria=met

If no rows are found, I would like text to contain '**Empty' for example


Thanks
ora_nut
· Share on TwitterShare on Facebook
«1

Comments

  • 105967
    105967 Member Posts: 1,027
    You have to code it yourself. Check if there is no row returned and if yes set the variable to your desired value.
    (just think about it, if there is NO ROW, how can a column be filled ie there is no column to be filled)
    · Share on TwitterShare on Facebook
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    e.g.
    SQL> var met number
SQL>
SQL> exec :met := 1

PL/SQL procedure successfully completed.

SQL>
SQL> with text_file as
  2  (
  3    select 1 criteria, 'some_text' text from dual
  4  )
  5  select nvl(text,'***empty') text from text_file t1, (select :met met from dual) where t1.criteria(+) = met
  6  /

TEXT
---------
some_text

SQL>
SQL> exec :met := 2

PL/SQL procedure successfully completed.

SQL>
SQL> /

TEXT
---------
***empty
    · Share on TwitterShare on Facebook
  • cubmar
    cubmar Member Posts: 89
    Well, I would perhaps do something like this

    select text from text_file where criteria=met
    UNION
    select '** No data' text from dual

    But of course only when no data is returned.
    · Share on TwitterShare on Facebook
  • cubmar
    cubmar Member Posts: 89
    Soemthing like this works, I guess:
      1  with text_file as
    
  2  (select 'This' text, 'met' criteria from dual)
    
  3  select nvl(text,dfttext) text from
    
  4  (select 1 lnk, text from text_file where criteria='met') qry
    
  5  ,
    
  6  (select 1 lnk, '** No data' dfttext from dual) dft
    
  7* where qry.lnk(+)=dft.lnk
    
  8  /

    
TEXT
    
----------
    
This
      1  with text_file as
    
  2  (select 'This' text, 'notmet' criteria from dual)
    
  3  select nvl(text,dfttext) text from
    
  4  (select 1 lnk, text from text_file where criteria='met') qry
    
  5  ,
    
  6  (select 1 lnk, '** No data' dfttext from dual) dft
    
  7* where qry.lnk(+)=dft.lnk
    
SQL> /

    
TEXT
    
----------
    
** No data
    It might not look too pretty!
    · Share on TwitterShare on Facebook
  • Keith Jamieson
    Keith Jamieson Member Posts: 2,815 Gold Badge
    As leo said, you have to code it.

    if sql%rowcount = 0
    <perform action>
    end if;

    or a local pl/sql block.

    begin
    <select statement >
    exception
    when no_data_found
    then
    perform action>
    end;
    · Share on TwitterShare on Facebook
  • cubmar
    cubmar Member Posts: 89
    Keith,

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

    Thanks
    · Share on TwitterShare on Facebook
  • Keith Jamieson
    Keith Jamieson Member Posts: 2,815 Gold Badge
    Try this and see what you get:

    select nvl(dummy,'A NULL') from dual where dummy='Y'

    The other solutions "Cheat" in that they actually force a row to be found.

    But maybe if you posted the problem you're trying to solve, ie the question behind your question we could help further.
    · Share on TwitterShare on Facebook
  • 105967
    105967 Member Posts: 1,027
    ... and where you easily can end up with a full table scan, ie a disastrous performance.
    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 42,311 Red Diamond
    And just for fun without using NVL or outer joins...
    SQL> ed
Wrote file afiedt.buf

  1  with t as (select 'fred' as nm from dual union all
  2             select 'bob' from dual union all
  3             select 'jim' from dual)
  4  -- end of test data
  5  select nm from (
  6    select rownum rn, nm from t where nm like '&name%'
  7    union
  8    select 0, '~~ NO DATA ~~' from dual
  9    order by 1 desc
 10    )
 11* where (rn > 0 or (rownum = 1 and rn = 0))
SQL> /
Enter value for name: will
old   6:   select rownum rn, nm from t where nm like '&name%'
new   6:   select rownum rn, nm from t where nm like 'will%'

NM
-------------
~~ NO DATA ~~

SQL> /
Enter value for name: fred
old   6:   select rownum rn, nm from t where nm like '&name%'
new   6:   select rownum rn, nm from t where nm like 'fred%'

NM
-------------
fred

SQL>
    · Share on TwitterShare on Facebook
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    ... and where you easily can end up with a full table scan, ie a disastrous performance
    Why should it be so? Of course a proper Index layout is assumed:
    SQL>  create table text_file as select object_id criteria, object_name text from all_objects
/
Table created.

SQL>  create unique index text_file_idx on text_file (criteria)
/
Index created.

SQL>  set autotrace on explain

SQL>  select nvl(text,'***empty') text from text_file t1, (select 15111 met from dual) where t1.criteria(+) = met
/
TEXT                          
------------------------------
/df245789_JavaToSQLMapMapClass
1 row selected.

Execution Plan
----------------------------------------------------------
           SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=3 Card=1 Bytes=30)
   1         NESTED LOOPS OUTER (Cost=3 Card=1 Bytes=30)
   2    1      FAST DUAL (Cost=2 Card=1)
   3    1      TABLE ACCESS BY INDEX ROWID MICHAEL.TEXT_FILE (Cost=1 Card=1 Bytes=30)
   4    3        INDEX UNIQUE SCAN MICHAEL.TEXT_FILE_IDX (Cost=0 Card=1)


SQL>  select nvl(text,'***empty') text from text_file t1, (select 106276 met from dual) where t1.criteria(+) = met
/
TEXT                          
------------------------------
***empty                      
1 row selected.


Execution Plan
----------------------------------------------------------
           SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=3 Card=1 Bytes=30)
   1         NESTED LOOPS OUTER (Cost=3 Card=1 Bytes=30)
   2    1      FAST DUAL (Cost=2 Card=1)
   3    1      TABLE ACCESS BY INDEX ROWID MICHAEL.TEXT_FILE (Cost=1 Card=1 Bytes=30)
   4    3        INDEX UNIQUE SCAN MICHAEL.TEXT_FILE_IDX (Cost=0 Card=1)

SQL>  drop table text_file
/
Table dropped.
    · Share on TwitterShare on Facebook
This discussion has been closed.