CODEWITHSUNDEEP
sqldatabaseoracle-database
beenz
beenz

Reputation: 47

in oracle, can I put string in num data type column?

my dataset

name     salary
JAY      100
ALI      200
WARREN   500
JOHN     null

I want to get the names and salary of the people at my table. However, if salary is a null value, I want the word 'no salary' to appear. I tried using nvl, but the data type is different and it cannot be executed. Is there any possible way?

Upvotes: 1

Views: 65

Answers (2)

user5683823
user5683823

Reputation:

Gordon Linoff showed you one way that will work - and it is absolutely critical that you follow this advice, only do this as the very last step in developing your report.

An even better option is to leave the query alone, and to use your front-end program (like SQL*Plus, or SQL Developer, or Toad, or whatever reporting software you use) to show whatever you need instead of null.

For example, in SQL*Plus you can use the SET command (note carefully that this is a SQL*Plus command, not a SQL statement; in particular, you don't end it with semicolon.

set null 'no salary'

Here is a brief demo - using the emp table in the scott schema. (I use it for the comm column; all salaries are non-null in the table, but some commissions are null.) First I show results with the default (nothing shown for null), then I show how I issue the SET command and then I re-run the query.

Note the significant advantage of doing it this way: even though we added "text" to the column, the non-null values are still displayed as numbers - meaning, they are still shown right-aligned. With Gordon Linoff's solution, everything in that column is considered strings, so all the numbers will be left-aligned, making the column harder to read.

SQL> select empno, ename, job, sal, comm from scott.emp where deptno = 30;

     EMPNO ENAME      JOB              SAL       COMM
---------- ---------- --------- ---------- ----------
      7499 ALLEN      SALESMAN        1600        300
      7521 WARD       SALESMAN        1250        500
      7654 MARTIN     SALESMAN        1250       1400
      7698 BLAKE      MANAGER         2850
      7844 TURNER     SALESMAN        1500          0
      7900 JAMES      CLERK            950

6 rows selected.

SQL> set null 'no comm'

SQL> select empno, ename, job, sal, comm from scott.emp where deptno = 30;

     EMPNO ENAME      JOB              SAL       COMM
---------- ---------- --------- ---------- ----------
      7499 ALLEN      SALESMAN        1600        300
      7521 WARD       SALESMAN        1250        500
      7654 MARTIN     SALESMAN        1250       1400
      7698 BLAKE      MANAGER         2850 no comm
      7844 TURNER     SALESMAN        1500          0
      7900 JAMES      CLERK            950 no comm

6 rows selected.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269933

You need to convert the value when you query the column:

select coalesce(to_char(salary), 'no salary')

Do not store the value as a string. NULL makes full sense for the database. You want to show this differently when you use the value.

Upvotes: 1

Related Questions