SQL Query For Displaying 'No Data Found' for values containing null and 0 along with other records

Question

The following was one of the practice questions in the online course i took on udemy for SQL.

I have a table 'emp' and consider the below names in the query map to their corresponding columns in table. In this table there is a column named 'comm' which is configured to accept number datatype. What i want is to retrieve data from the table in such a way that wherever the value in comm is empty(null) or 0, i want to display 'No data found' and wherever there is a value, it should show that number normally.

I executed the below query and it worked. But i'm looking for another concise and efficient way to write this, maybe using subquery or something else, i don't know.

select ename,sal,job,'No data found'as comm from emp
where comm is NULL or comm=0
union
select ename,sal,job,to_char(comm) as comm from emp where
comm is not null and comm!=0

In the query after union, i had to use to_char to avoid datatype error. Which makes me think that even when we are displaying data using select statements and not inserting/updating the record, the data type of the values in column should be same. Is it ? I was under the impression that since we are displaying data, we can display it however we want it. Please do comment on this also.

Appreciate your suggestions/thoughts here!

Answer 1

An example with CASE statement:

select
  ename, sal, job,
  case
    when comm is null or comm = 0 then 'No data found'
    else to_char(comm)
  end as comm
from
  emp
;

The UNION [ALL], INTERSECT, MINUS Operators

The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same data type group (such as numeric or character).