Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Returning a default value in SQL when no rows found

cubmar
Member Posts: 89
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
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
Comments
-
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) -
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
-
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. -
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
----------
This1 with text_file as
It might not look too pretty!
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 -
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; -
Keith,
There are two (very similar) examples of how to do it without the need for PL/SQL
Thanks -
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. -
... and where you easily can end up with a full table scan, ie a disastrous performance.
-
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>
-
... and where you easily can end up with a full table scan, ie a disastrous performanceWhy 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.
This discussion has been closed.