Oracle query to display some template message if the data for a particular value is not present?

Question

I want to display some template message for the value which are not present in the table.

Example:

select table_name from all_tables where table_name in('RAM','SHA,','EMPLOYEE','E_SCHOOL');

out of the given 4 tables above only EMPLOYEE table is there so I want to display no value for the other result instead of not displaying any value.

I tried nvl for this as

select nvl(table_name,'NO VALUE') from all_tables where table_name in('RAM','SHA,','EMPLOYEE','E_SCHOOL');

but still the result is not coming

any suggestion to get the reasult..?

Thanks in advance and please forgive for any mistake as I am beginner in DB

score 0 · Answer 1 · answered Jan 24 '13 at 13:46

Your query selects all records in all_tables that have a table_name of RAM, SHA, EMPLOYEE or E_SCHOOL. If only EMPLOYEE exists, then only that record will be returned and, when you use NVL -- which does what you think it does -- it will correctly display the table name. You are confused on the usage of in.

To do what you're looking for is more complicated:

with tablesImLookingFor as (
  select 'RAM' table_name from dual union all
  select 'SHA' table_name from dual union all
  select 'EMPLOYEE' table_name from dual union all
  select 'E_SCHOOL' table_name from dual
)
select    tablesImLookingFor.table_name,
          nvl(all_tables.table_name, 'Not found') table_exists
from      tablesImLookingFor
left join all_tables
on        all_tables.table_name = tablesImLookingFor.table_name;

...which would return something like:

table_name   table_exists
============ =============
RAM          Not found
SHA          Not found
EMPLOYEE     EMPLOYEE
E_SCHOOL     Not found

However, there are better ways to achieve checking for a match; using an inner join would do it without all the nvl business.

Anyway, to summarise, you are using NVL correctly.

Thanks for replying ... but I have a no of table_name values so I want be able to write all as select 'xvale" from dual union all select 'yval' from dual .... .... .. — Learner, Jan 24 '13 at 14:06
That's the only way it can be done. If you don't want to explicitly include all these tables in a CTE, then you can create a table with the names you want, then join against that. — Xophmeister, Jan 24 '13 at 14:20

score 0 · Answer 2 · answered Jan 24 '13 at 13:46

0

with table_names (table_name) as (
    select 'RAM' from dual
    union all
    select 'SHA' from dual
    union all 
    select 'EMPLOYEE' from dual
    union all 
    select 'E_SCHOOL' from dual
)
select nvl(at.table_name, 'NO VALUE')
from table_names tn
  left join all_tables at on at.table_name = tn.table_name

answered Jan 24 '13 at 13:46

Thanks for replying ... but I have a no of table_name values so I want be able to write all as select 'xvale" from dual union all select 'yval' from dual .... .... .. – Learner Jan 24 '13 at 14:04
@Jagdeep: then you can't get an "empty" row for tables that are not there. – Jan 24 '13 at 14:08

score 0 · Answer 3 · answered Jan 24 '13 at 15:01

im confused, as the other two answers are perfectly ok. is it that you don't want to type "from dual" etc all the time? if so a shorter version is:

create type varchar2_tab as table of varchar2(30);
/
with tables as (select value(t) table_name
                  from table(varchar2_tab('RAM','SHA','EMPLOYEE','E_SCHOOL')) t)
select my_tab.table_name, nvl2(t.table_name, 'FOUND', 'NOT FOUND')
  from tables my_tab
       left outer join all_tables t
                    on t.table_name = my_tab.table_name;

or if you can't create your own type:

with tables as (select value(t) table_name
                  from table(sys.DBMSOUTPUT_LINESARRAY(
                          'RAM','SHA','EMPLOYEE','E_SCHOOL')) t)
select my_tab.table_name, nvl2(t.table_name, 'FOUND', 'NOT FOUND')
  from tables my_tab
       left outer join all_tables t
                    on t.table_name = my_tab.table_name;

you can see the available public collections with

 select owner, type_name, coll_type, upper_bound, length 
   from all_coll_types 
  where elem_type_name = 'VARCHAR2' and length >= 30

with ALL_TABLES as you have, you're looking over many schemas without including an owner clause. you should put on (n t.table_name = my_tab.table_name and t.owner = 'XX') or , if your connected as the table owner, just use the user view

score 0 · Answer 4 · answered Jan 24 '13 at 15:06

If you want to display every row, just displaying NO VALUE for a select few, you don't need to join to a separate table. You can just convert the values in your select clause:

select case when table_name in ('RAM', 'SHA', 'E_SCHOOL') then
         'NO VALUE'
       else
         table_name
       end table_name
from   all_tables;

Oracle query to display some template message if the data for a particular value is not present?

4 Answers4