SQL command not properly ended?

[outhowz42]

This works in mySQL, but not Oracle 10g and I have no idea why. Please help!

SQL> SELECT E.name, B.publisher FROM employee as E
2 INNER JOIN loan AS L ON (E.empno=L.empno)
3 INNER JOIN books as B ON (L.isbn=B.isbn)
4 GROUP BY E.name, B.publisher
5 HAVING COUNT(DISTINCT B.isbn)>5;


ERROR at line 1:
ORA-00933: SQL command not properly ended

How should this be written in Oracle?

[Dave44]

This works in mySQL, but not Oracle 10g and I have no idea why. Please help!

SQL> SELECT E.name, B.publisher FROM employee as E
2 INNER JOIN loan AS L ON (E.empno=L.empno)
3 INNER JOIN books as B ON (L.isbn=B.isbn)
4 GROUP BY E.name, B.publisher
5 HAVING COUNT(DISTINCT B.isbn)>5;


ERROR at line 1:
ORA-00933: SQL command not properly ended

How should this be written in Oracle?

your table name alias cant have the AS in it

[145]dave@> select * from dual as d;
select * from dual as d
                   *
ERROR at line 1:
ORA-00933: SQL command not properly ended
 
 
Elapsed: 00:00:00.01
[145]dave@> select * from dual d;
 
D
-
X
 
Elapsed: 00:00:00.01
 

[subashsavji]

This works in mySQL, but not Oracle 10g and I have no idea why. Please help!

SQL> SELECT E.name, B.publisher FROM employee as E
2 INNER JOIN loan AS L ON (E.empno=L.empno)
3 INNER JOIN books as B ON (L.isbn=B.isbn)
4 GROUP BY E.name, B.publisher
5 HAVING COUNT(DISTINCT B.isbn)>5;


ERROR at line 1:
ORA-00933: SQL command not properly ended

How should this be written in Oracle?

like this, may be it will be helpful to you

SELECT e.empno,E.ename,B.dname FROM emp E
INNER JOIN dept b ON (E.deptno=b.deptno)
GROUP BY E.ename, B.dname,e.empno
HAVING COUNT(DISTINCT e.empno)>5

[debasisdas]

In oracle AS is used for alias of column names .For table aliasing the alias name must immediately follow the table name without AS keyword.

Just try to use this .

SELECT E.name, B.publisher FROM employee  E
    INNER JOIN loan  L ON (E.empno=L.empno)
    INNER JOIN books  B ON (L.isbn=B.isbn)
    GROUP BY E.name, B.publisher
    HAVING COUNT(DISTINCT B.isbn)>5;

[outhowz42]

Thanks everyone... I got it working now!!