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,'19 83-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','S ALESMAN',7698,' 1984-03-26',1250,500,30 );
INSERT INTO EMP
VALUES (7566,'JONES',' MANAGER',7839,' 1983-10-31',2975,NULL,2 0);
INSERT INTO EMP
VALUES (7654,'MARTIN', 'SALESMAN',7698 ,'1983-12-05',1250,1400,3 0);
INSERT INTO EMP
VALUES (7698,'BLAKE',' MANAGER',7839,' 1984-06-11',2850,NULL,3 0);
INSERT INTO EMP
VALUES (7782,'CLARK',' MANAGER',7839,' 1984-05-14',2450,NULL,1 0);
INSERT INTO EMP
VALUES (7788,'SCOTT',' ANALYST',7566,' 1984-03-05',3000,NULL,2 0);
INSERT INTO EMP
VALUES (7839,'KING','P RESIDENT',NULL, '1984-07-09',5000,NULL,1 0);
INSERT INTO EMP
VALUES (7844,'TURNER', 'SALESMAN',7698 ,'1984-06-04',1500,0,30);
INSERT INTO EMP
VALUES (7876,'ADAMS',' CLERK',7788,'19 84-06-04',1100,NULL,2 0);
INSERT INTO EMP
VALUES (7900,'JAMES',' CLERK',7698,'19 84-07-23',950,NULL,30 );
INSERT INTO EMP
VALUES (7902,'FORD','A NALYST',7566,'1 983-12-05',3000,NULL,2 0);
INSERT INTO EMP
VALUES (7934,'MILLER', 'CLERK',7782,'1 983-11-21',1300,NULL,1 0);
INSERT INTO DEPT
VALUES (10,'ACCOUNTING ','NEW YORK');
INSERT INTO DEPT
VALUES (20,'RESEARCH', 'DALLAS');
INSERT INTO DEPT
VALUES (30,'SALES','CH ICAGO');
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/