For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
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
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!
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>
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.
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}
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 )
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