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!