1
SQL> select last_name, job_id, salary, commission_pct from employees;

LAST_NAME                 JOB_ID         SALARY COMMISSION_PCT                                                                                                                                          
------------------------- ---------- ---------- --------------                                                                                                                                          
King                      AD_PRES         24000                                                                                                                                                         
Kochhar                   AD_VP           17000                                                                                                                                                         
De Haan                   AD_VP           17000                                                                                                                                                         
Hunold                    IT_PROG          9000                                                                                                                                                         
Ernst                     IT_PROG          6000                                                                                                                                                         
Austin                    IT_PROG          4800                                                                                                                                                         
Pataballa                 IT_PROG          4800                                                                                                                                                         
Lorentz                   IT_PROG          4200                                                                                                                                                         
Greenberg                 FI_MGR          12008                                                                                                                                                         
Faviet                    FI_ACCOUNT       9000                                                                                                                                                         
Chen                      FI_ACCOUNT       8200                                                                                                                                                         
Sciarra                   FI_ACCOUNT       7700                                                                                                                                                         
Urman                     FI_ACCOUNT       7800                                                                                                                                                         
Popp                      FI_ACCOUNT       6900                                                                                                                                                         
Raphaely                  PU_MAN          11000                                                                                                                                                         
Khoo                      PU_CLERK         3100                                                                                                                                                         
Baida                     PU_CLERK         2900                                                                                                                                                         
Tobias                    PU_CLERK         2800                                                                                                                                                         
Himuro                    PU_CLERK         2600                                                                                                                                                         
Colmenares                PU_CLERK         2500                                                                                                                                                         
Weiss                     ST_MAN           8000                                                                                                                                                         
Fripp                     ST_MAN           8200                                                                                                                                                         
Kaufling                  ST_MAN           7900                                                                                                                                                         
Vollman                   ST_MAN           6500                                                                                                                                                         
Mourgos                   ST_MAN           5800                                                                                                                                                         
Nayer                     ST_CLERK         3200                                                                                                                                                         
Mikkilineni               ST_CLERK         2700                                                                                                                                                         
Landry                    ST_CLERK         2400                                                                                                                                                         
Markle                    ST_CLERK         2200                                                                                                                                                         
Bissot                    ST_CLERK         3300                                                                                                                                                         
Atkinson                  ST_CLERK         2800                                                                                                                                                         
Marlow                    ST_CLERK         2500                                                                                                                                                         
Olson                     ST_CLERK         2100                                                                                                                                                         
Mallin                    ST_CLERK         3300                                                                                                                                                         
Rogers                    ST_CLERK         2900                                                                                                                                                         
Gee                       ST_CLERK         2400                                                                                                                                                         
Philtanker                ST_CLERK         2200                                                                                                                                                         
Ladwig                    ST_CLERK         3600                                                                                                                                                         
Stiles                    ST_CLERK         3200                                                                                                                                                         
Seo                       ST_CLERK         2700                                                                                                                                                         
Patel                     ST_CLERK         2500                                                                                                                                                         
Rajs                      ST_CLERK         3500                                                                                                                                                         
Davies                    ST_CLERK         3100                                                                                                                                                         
Matos                     ST_CLERK         2600                                                                                                                                                         
Vargas                    ST_CLERK         2500                                                                                                                                                         
Russell                   SA_MAN          14000             .4                                                                                                                                          
Partners                  SA_MAN          13500             .3                                                                                                                                          
Errazuriz                 SA_MAN          12000             .3                                                                                                                                          
Cambrault                 SA_MAN          11000             .3                                                                                                                                          
Zlotkey                   SA_MAN          10500             .2                                                                                                                                          
Tucker                    SA_REP          10000             .3                                                                                                                                          
Bernstein                 SA_REP           9500            .25                                                                                                                                          
Hall                      SA_REP           9000            .25                                                                                                                                          
Olsen                     SA_REP           8000             .2                                                                                                                                          
Cambrault                 SA_REP           7500             .2                                                                                                                                          
Tuvault                   SA_REP           7000            .15                                                                                                                                          
King                      SA_REP          10000            .35                                                                                                                                          
Sully                     SA_REP           9500            .35                                                                                                                                          
McEwen                    SA_REP           9000            .35                                                                                                                                          
Smith                     SA_REP           8000             .3                                                                                                                                          
Doran                     SA_REP           7500             .3                                                                                                                                          
Sewall                    SA_REP           7000            .25                                                                                                                                          
Vishney                   SA_REP          10500            .25                                                                                                                                          
Greene                    SA_REP           9500            .15                                                                                                                                          
Marvins                   SA_REP           7200             .1                                                                                                                                          
Lee                       SA_REP           6800             .1                                                                                                                                          
Ande                      SA_REP           6400             .1                                                                                                                                          
Banda                     SA_REP           6200             .1                                                                                                                                          
Ozer                      SA_REP          11500            .25                                                                                                                                          
Bloom                     SA_REP          10000             .2                                                                                                                                          
Fox                       SA_REP           9600             .2                                                                                                                                          
Smith                     SA_REP           7400            .15                                                                                                                                          
Bates                     SA_REP           7300            .15                                                                                                                                          
Kumar                     SA_REP           6100             .1                                                                                                                                          
Abel                      SA_REP          11000             .3                                                                                                                                          
Hutton                    SA_REP           8800            .25                                                                                                                                          
Taylor                    SA_REP           8600             .2                                                                                                                                          
Livingston                SA_REP           8400             .2                                                                                                                                          
Grant                     SA_REP           7000            .15                                                                                                                                          
Johnson                   SA_REP           6200             .1                                                                                                                                          
Taylor                    SH_CLERK         3200                                                                                                                                                         
Fleaur                    SH_CLERK         3100                                                                                                                                                         
Sullivan                  SH_CLERK         2500                                                                                                                                                         
Geoni                     SH_CLERK         2800                                                                                                                                                         
Sarchand                  SH_CLERK         4200                                                                                                                                                         
Bull                      SH_CLERK         4100                                                                                                                                                         
Dellinger                 SH_CLERK         3400                                                                                                                                                         
Cabrio                    SH_CLERK         3000                                                                                                                                                         
Chung                     SH_CLERK         3800                                                                                                                                                         
Dilly                     SH_CLERK         3600                                                                                                                                                         
Gates                     SH_CLERK         2900                                                                                                                                                         
Perkins                   SH_CLERK         2500                                                                                                                                                         
Bell                      SH_CLERK         4000                                                                                                                                                         
Everett                   SH_CLERK         3900                                                                                                                                                         
McCain                    SH_CLERK         3200                                                                                                                                                         
Jones                     SH_CLERK         2800                                                                                                                                                         
Walsh                     SH_CLERK         3100                                                                                                                                                         
Feeney                    SH_CLERK         3000                                                                                                                                                         
OConnell                  SH_CLERK         2600                                                                                                                                                         
Grant                     SH_CLERK         2600                                                                                                                                                         
Whalen                    AD_ASST          4400                                                                                                                                                         
Hartstein                 MK_MAN          13000                                                                                                                                                         
Fay                       MK_REP           6000                                                                                                                                                         
Mavris                    HR_REP           6500                                                                                                                                                         
Baer                      PR_REP          10000                                                                                                                                                         
Higgins                   AC_MGR          12008                                                                                                                                                         
Gietz                     AC_ACCOUNT       8300  

And I tried the following query

SQL> select last_name, job_id, salary, commission_pct from employees where commission_pct=nvl(commission_pct,0);

It only shows employees that have a commission. But I want only those people that who have not commission.

  • LAST_NAME JOB_ID SALARY COMMISSION_PCTRussell SA_MAN 14000 .4 – surendra Aug 23 '17 at 5:33
  • oracle dbms for practising – surendra Aug 23 '17 at 6:57
1

The Oracle function NVL(expression, value) converts any NULL values found in the expression to a value specified.

Reference: NVL (Oracle Documentation)

Your statement actually checks each value of commission_pct and if it is NULL converts it to 0.

You then compare the column commission_pct of every record returned which had a NULL value to the value 0. This is never true.

Example

Atkinson               ST_CLERK        2500     (NULL)

So running your statement:

select last_name, job_id, salary, commission_pct from employees where commission_pct=nvl(commission_pct,0);

....results in the case of Atkinson:

WHERE NULL = 0

Explanation

The value of ATKINSON's commision_pct is NULL. When the record is retrieved by the database engine it checks the WHERE clause, and compares the left side of the expression (which is NULL for ATKINSON) and compares it with the converted value of NULL which is then 0.

This is never true.

Solution

SQL> SELECT last_name, job_id, salary, commission_pct from employees where commission_pct IS NULL;
| improve this answer | |
  • SQL> select salary , commission_pct from employees where commission_pct in null; no rows selected – surendra Aug 23 '17 at 7:16
  • sorry it works,,,......... thanks a lot for your explanation. – surendra Aug 23 '17 at 7:18
  • 1
    @surendra: if the answer solved your problem, please accept it so that your question is marked as resolved – a_horse_with_no_name Aug 23 '17 at 8:53
0

Doesn't this work?

SQL> select last_name, job_id, salary, commission_pct 
from employees 
where commission_pct IS NULL;

Or if the table definition for the column commission_pct is different you can try:

SQL> select last_name, job_id, salary, commission_pct 
from employees 
where commission_pct > 0 
and commission_pct < 0;
| improve this answer | |
  • where commission_pct > 0 and commission_pct < 0 is never true. How can a single value be greater then zero and smaller then zero? – a_horse_with_no_name Aug 23 '17 at 7:22
  • my bad, equal signs are needed – anisakras Aug 23 '17 at 8:49

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.