1

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!

2

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.