suredazzle
asked on
Job <> Manager or Clerk Has Null in Comm
Hi,
I want to find emp job ain't manager or clerk.
!=, still doesn't work.
Does null in Comm reflect output? Should Null include SQL?
select empno, ename, deptno, job
from emp
where (job <> 'manager' OR job <> 'clerk')
and deptno = 10;
==========================
/* Table */
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7900 JAMES CLERK 7698 03-DEC-81 950
30
7934 MILLER CLERK 7782 23-JAN-82 1300
10
========================
Output
EMPNO ENAME DEPTNO JOB
---------- ---------- ---------- ---------
7839 KING 10 PRESIDENT
7782 CLARK 10 MANAGER
7934 MILLER 10 CLERK
I want to find emp job ain't manager or clerk.
!=, still doesn't work.
Does null in Comm reflect output? Should Null include SQL?
select empno, ename, deptno, job
from emp
where (job <> 'manager' OR job <> 'clerk')
and deptno = 10;
==========================
/* Table */
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7900 JAMES CLERK 7698 03-DEC-81 950
30
7934 MILLER CLERK 7782 23-JAN-82 1300
10
========================
Output
EMPNO ENAME DEPTNO JOB
---------- ---------- ---------- ---------
7839 KING 10 PRESIDENT
7782 CLARK 10 MANAGER
7934 MILLER 10 CLERK
you can use != instead of <> if you want to
ASKER
Hi Sdstuber,
Tried both ways, still not working.
SQL> select empno, ename, deptno, job
from emp
where job != 'manager'
and job != 'clerk'
and deptno = 10; 2 3 4 5
EMPNO ENAME DEPTNO JOB
---------- ---------- ---------- ---------
7839 KING 10 PRESIDENT
7782 CLARK 10 MANAGER
7934 MILLER 10 CLERK
Not too familiar with Null, it shoud be "Not equal". Right?
Let me see what I find on NULL.
Tried both ways, still not working.
SQL> select empno, ename, deptno, job
from emp
where job != 'manager'
and job != 'clerk'
and deptno = 10; 2 3 4 5
EMPNO ENAME DEPTNO JOB
---------- ---------- ---------- ---------
7839 KING 10 PRESIDENT
7782 CLARK 10 MANAGER
7934 MILLER 10 CLERK
Not too familiar with Null, it shoud be "Not equal". Right?
Let me see what I find on NULL.
you don't have any nulls though. if you do care about null then use "IS NULL" or "IS NOT NULL"
in this case you also have case sensitivity, sorry I didn't notice that earlier
clerk != CLERK
manager != MANAGER
SELECT empno, ename, deptno, JOB
FROM emp
WHERE JOB != 'MANAGER'
AND JOB != 'CLERK'
AND deptno = 10;
you could also use not in
SELECT empno, ename, deptno, JOB
FROM emp
WHERE JOB not in ('MANAGER','CLERK')
AND deptno = 10;
in this case you also have case sensitivity, sorry I didn't notice that earlier
clerk != CLERK
manager != MANAGER
SELECT empno, ename, deptno, JOB
FROM emp
WHERE JOB != 'MANAGER'
AND JOB != 'CLERK'
AND deptno = 10;
you could also use not in
SELECT empno, ename, deptno, JOB
FROM emp
WHERE JOB not in ('MANAGER','CLERK')
AND deptno = 10;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Sdstuber,
I used your solution. It works! Thanks very much.
Gosh, it is case sensitive.
I used your solution. It works! Thanks very much.
Gosh, it is case sensitive.
glad I could help
ASKER
Hi Sdstuber,
I used your solution. It works! Thanks very much. I learn a lot.
Gosh, it is case sensitive.
I used your solution. It works! Thanks very much. I learn a lot.
Gosh, it is case sensitive.
any job will either be MANGER or CLERK or neither.
So, every job is <> MANGER or <> CLERK or both.
try this...
select empno, ename, deptno, job
from emp
where job <> 'manager'
AND job <> 'clerk'
and deptno = 10;