I am validating Firebird over open access commands. For validating,
I use the same command over Firebird, open access with the same data and schema.
FROM EMP E
WHERE 1 = (SELECT MAX(DEPTNO)
FROM DEPT D
WHERE E.ENAME like '%AME%')
OR EMPNO = (SELECT MAX(DEPTNO)
FROM DEPT D
WHERE E.ENAME like (SELECT TOP 1 ENAME
FROM emp E1
ORDER BY EMPNO;
Rows selected = 27
Rows selected = 0
CREATE TABLE EMP(EMPNO integer , ENAME varchar(32) , JOB varchar(32) , HIREDATE datetime , SAL float ,COMM real ,DEPTNO integer);
CREATE TABLE DEPT(DEPTNO integer, DNAME varchar(40), LOC varchar(40), DIVNO integer);
INSERT INTO EMP VALUES(101, 'NAME1', 'TITLE1', '1990-02-02 02:02:02', 100500.55, 2000.90, 1);
INSERT INTO EMP VALUES (102,'NAME2','TITLE2', '1958-02-02 02:02:02',2000.00, 20000.00, 1);
INSERT INTO EMP VALUES(103, 'NAME3', 'TITLE3', '1997-03-12 08:09:54', 3000.00, 1000.50, 2);
INSERT INTO EMP VALUES(104, 'NAME4', 'TITLE4', '1997-04-02 00:00:00', 1111.11, 2.22, 1);
INSERT INTO EMP VALUES(105, 'NAME5', 'TITLE5', '1997-06-12 08:09:54', 3000.00, 1000.50, 2);
INSERT INTO EMP VALUES(106, 'NAME6', 'TITLE6', '1997-06-12 02:02:02', 1111.11, 2.22, 1);
INSERT INTO DEPT (DEPTNO, DNAME, LOC, DIVNO) Values (1, 'Software', 'San Jose', 10);
INSERT INTO DEPT (DEPTNO, DNAME, LOC, DIVNO) Values (2, 'Sales', 'LA', 20);
INSERT INTO DEPT (DEPTNO, DNAME, LOC, DIVNO) Values (3, 'Marketing', 'LA', 20);
The reasons that you don't get any information is simple, the data you have doesn't allow for any data to be returned. Lets dissect your query:
SELECT EMPNO,SAL FROM EMP E
Select some fields from emp
WHERE 1 = (SELECT MAX(DEPTNO) FROM DEPT D WHERE E.ENAME like '%AME%')
Where 1 must be equal to the max
DEPT if for the current row from
EMP the field
AME (note that this is case sensitive).
This means that the comparison is either
1 = null (which is
null, not false) or - with your sample data -
1 = 3 (which is false).
OR EMPNO = (SELECT MAX(DEPTNO) FROM DEPT D WHERE E.ENAME like (SELECT TOP 1 ENAME FROM emp E1 WHERE E1.EMPNO=D.DEPTNO))
EMPNO is equal to the maximum of
DEPT with a convoluted query that makes no sense: you are querying the maximum
DEPTNO where the
ENAME of the current row of
EMP E is equal to any first
ENAME for where the
EMPNO is equal to the current
DEPT D (which given the sample data is always false).
This query will always yield
null because - in the sample data - the values in
EMPNO are disjunct from
DEPTNO, so the result of the innermost query is no row (yields null), so the first inner query will also yield
null. Even if the first inner query had returned a value, the evaluation would still be false as
DEPTNO are disjunct.
In other words, the
WHERE-clause is equivalent to
WHERE FALSE OR NULL, which yields no rows.
I have no idea what open access is (because a quick search doesn't reveal anything called that), but it seems to have very weird evaluation rules for SQL queries.
As I don't understand what this query is trying to retrieve, I also can't offer you a query that works.