MySQL 4.0.15 vs. Oracle query ARRGH

[Asfand Yar Qazi]

Ahem..
Anyway, here's whats happening...

construct tables in MySQL:
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO INT(4) NOT NULL,
ENAME CHAR(6) NOT NULL,
JOB CHAR(9),
MGR INT(4),
HIREDATE DATE NOT NULL,
SAL DOUBLE(6,2),
COMM DOUBLE(6,2),
DEPTNO INT(2) NOT NULL);
DROP TABLE DEPT;
CREATE TABLE DEPT
(DEPTNO INT(2) NOT NULL,
DNAME CHAR(10) NOT NULL,
LOC CHAR(8));
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
(GRADE INT(1) NOT NULL,
LOSAL INT(4) NOT NULL,
HISAL INT(4) NOT NULL);
CREATE INDEX EMPNOIND ON EMP (EMPNO);
CREATE INDEX DEPTNOEMPIND ON EMP (DEPTNO);
CREATE INDEX DEPTNODEPTIND ON DEPT (DEPTNO);
INSERT INTO EMP
VALUES (7369,'SMITH','CLERK',7902,'1983-06-13',800,NULL,20);
INSERT INTO EMP
VALUES (7499,'ALLEN','SALESMAN',7698,'1983-08-15',1600,300,30);
INSERT INTO EMP
VALUES (7521,'WARD','SALESMAN',7698,'1984-03-26',1250,500,30);
INSERT INTO EMP
VALUES (7566,'JONES','MANAGER',7839,'1983-10-31',2975,NULL,20);
INSERT INTO EMP
VALUES (7654,'MARTIN','SALESMAN',7698,'1983-12-05',1250,1400,30);
INSERT INTO EMP
VALUES (7698,'BLAKE','MANAGER',7839,'1984-06-11',2850,NULL,30);
INSERT INTO EMP
VALUES (7782,'CLARK','MANAGER',7839,'1984-05-14',2450,NULL,10);
INSERT INTO EMP
VALUES (7788,'SCOTT','ANALYST',7566,'1984-03-05',3000,NULL,20);
INSERT INTO EMP
VALUES (7839,'KING','PRESIDENT',NULL,'1984-07-09',5000,NULL,10);
INSERT INTO EMP
VALUES (7844,'TURNER','SALESMAN',7698,'1984-06-04',1500,0,30);
INSERT INTO EMP
VALUES (7876,'ADAMS','CLERK',7788,'1984-06-04',1100,NULL,20);
INSERT INTO EMP
VALUES (7900,'JAMES','CLERK',7698,'1984-07-23',950,NULL,30);
INSERT INTO EMP
VALUES (7902,'FORD','ANALYST',7566,'1983-12-05',3000,NULL,20);
INSERT INTO EMP
VALUES (7934,'MILLER','CLERK',7782,'1983-11-21',1300,NULL,10);
INSERT INTO DEPT
VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT
VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT
VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT
VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO SALGRADE
VALUES (1,700,1200);
INSERT INTO SALGRADE
VALUES (2,1201,1400);
INSERT INTO SALGRADE
VALUES (3,1401,2000);
INSERT INTO SALGRADE
VALUES (4,2001,3000);
INSERT INTO SALGRADE
VALUES (5,3001,9999);
Based on identical tables (apart from obvious differences) in Oracle
SQL*Plus 8.

Oracle query entered:
SELECT ENAME, SAL, COMM
FROM EMP
WHERE JOB = 'SALESMAN' AND COMM > (5.0/100.0)*SAL;

Output on Oracle:

ENAME SAL COMM
------ ---------- ----------
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400

This is the correct output.
MySQL query entered:
SELECT ENAME, SAL, COMM
FROM EMP
WHERE JOB = 'SALESMAN' AND C0MM IS NULL;

Output on MySQL:
ERROR 1054: Unknown column 'C0MM' in 'where clause'
As subject says, ARRRRRGH!

Thanks,
Asfand Yar

--
http://www.it-is-truth.org/

[Asfand Yar Qazi]

Aggro wrote:

Asfand Yar Qazi wrote:

Ahem..
Anyway, here's whats happening...

construct tables in MySQL:
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO INT(4) NOT NULL,
ENAME CHAR(6) NOT NULL,
JOB CHAR(9),
MGR INT(4),
HIREDATE DATE NOT NULL,
SAL DOUBLE(6,2),
COMM DOUBLE(6,2),
DEPTNO INT(2) NOT NULL);

MySQL query entered:
SELECT ENAME, SAL, COMM
FROM EMP
WHERE JOB = 'SALESMAN' AND C0MM IS NULL;

Output on MySQL:
ERROR 1054: Unknown column 'C0MM' in 'where clause'

You have created a table with column name COMM (with o like outstanding)
and you are writing here "AND C0MM IS NULL;" ( where you are using 0
like 0,1,2,3,4,5...) ..

So replace the 0(number) with O(alphabet) and you should be fine.

Sorry, silly me...

--
http://www.it-is-truth.org/

[Asfand Yar Qazi]

Aggro wrote:

Asfand Yar Qazi wrote:

Ahem..
Anyway, here's whats happening...

construct tables in MySQL:
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO INT(4) NOT NULL,
ENAME CHAR(6) NOT NULL,
JOB CHAR(9),
MGR INT(4),
HIREDATE DATE NOT NULL,
SAL DOUBLE(6,2),
COMM DOUBLE(6,2),
DEPTNO INT(2) NOT NULL);

MySQL query entered:
SELECT ENAME, SAL, COMM
FROM EMP
WHERE JOB = 'SALESMAN' AND C0MM IS NULL;

Output on MySQL:
ERROR 1054: Unknown column 'C0MM' in 'where clause'

You have created a table with column name COMM (with o like outstanding)
and you are writing here "AND C0MM IS NULL;" ( where you are using 0
like 0,1,2,3,4,5...) ..

So replace the 0(number) with O(alphabet) and you should be fine.

Sorry, silly me...

--
http://www.it-is-truth.org/

[Aggro]

Asfand Yar Qazi wrote:

Ahem..
Anyway, here's whats happening...

construct tables in MySQL:
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO INT(4) NOT NULL,
ENAME CHAR(6) NOT NULL,
JOB CHAR(9),
MGR INT(4),
HIREDATE DATE NOT NULL,
SAL DOUBLE(6,2),
COMM DOUBLE(6,2),
DEPTNO INT(2) NOT NULL); MySQL query entered:
SELECT ENAME, SAL, COMM
FROM EMP
WHERE JOB = 'SALESMAN' AND C0MM IS NULL;

Output on MySQL:
ERROR 1054: Unknown column 'C0MM' in 'where clause'

You have created a table with column name COMM (with o like outstanding)
and you are writing here "AND C0MM IS NULL;" ( where you are using 0
like 0,1,2,3,4,5...) ..

So replace the 0(number) with O(alphabet) and you should be fine.

[Asfand Yar Qazi]

Aggro wrote:

Asfand Yar Qazi wrote:

Ahem..
Anyway, here's whats happening...

construct tables in MySQL:
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO INT(4) NOT NULL,
ENAME CHAR(6) NOT NULL,
JOB CHAR(9),
MGR INT(4),
HIREDATE DATE NOT NULL,
SAL DOUBLE(6,2),
COMM DOUBLE(6,2),
DEPTNO INT(2) NOT NULL);

MySQL query entered:
SELECT ENAME, SAL, COMM
FROM EMP
WHERE JOB = 'SALESMAN' AND C0MM IS NULL;

Output on MySQL:
ERROR 1054: Unknown column 'C0MM' in 'where clause'

You have created a table with column name COMM (with o like outstanding)
and you are writing here "AND C0MM IS NULL;" ( where you are using 0
like 0,1,2,3,4,5...) ..

So replace the 0(number) with O(alphabet) and you should be fine.

Sorry, silly me...

--
http://www.it-is-truth.org/