SQL command not properly ended - but i'm sure it's ok :(
SELECT S.FNAME, S.LNAME, COUNT(C.CNO)
FROM STUDENTS S,ENROLLS E,COURSES C
WHERE S.SID = E.SID AND E.TERM=C.TERM AND C.LINENO=E.LINENO
GROUP BY S.FNAME,S.LNAME,S.SID
ORDER BY COUNT(C.CNO)
HAVING COUNT(C.CNO) >= ALL(SELECT COUNT(C1.CNO)
FROM STUDENTS S1,ENROLLS E1,COURSES C
WHERE S1.SID=E1.SID AND E1.TERM=C1.TERM AND C1.LINENO=E1.LINENO
GROUP BY S1.SID);
i'm trying to find student who took most course.
all - it should compare the value with all the results isn't it ?
statements before ">=" is working fine andalso statements after ">=" is working fine. but when i write them together with "ALL" it says not properly ended .
i'm tryed to resolve it for 3 hours still no clue
Plz help me.
EDIT: Sorry for the spam. But this forum is wonderful as soon as i post here i got my solution bymyself.
Thx guys. If any of the moderator is reading this thread plz delete this thread plz.
solution is probably switching the ORDER BY and HAVING clauses into their proper sequence
i say probably because i'm not really an oracle sql expert, who knows what kind of wacko sql it supports
Oracle complies with the ANSI standard here, so yes the ORDER BY should come after the HAVING.
Actually I think Oracle complies a lot more to the ANSI standard than MS SQL and MySQL.
Just thinking about String concatenation, quoting identifiers and case sensitivity without quotes (those are the ones that I always stumble over...)
Well, no database fully supports the full standard
oracle thinks NULL and the empty string are the same thing -- worst misteak evar
Actually the more I work with it the more I like it. At the beginning I thought it was rubbish as well. In most of projects an empty string means "unknown" (and besides no one ever seems to need the concept of an "empty string" for integers, dates, floats, ...). But let's no take that road...
oracle doesn't allow the keyword AS for aliases
You mean for a table alias, it does allow it for a column alias.
oracle doesn't support row constructors, and neither does MS SQL -- yes, it's optional, but very handy, this feature
Yes, I agree very handy feature.
oracle doesn't support INFORMATION_SCHEMA
But I'm sure there are more queries out there that use string concatination using || than queries needing the INFORMATION_SCHEMA
- You mean for a table alias, it does allow it for a column alias.
Unless we fool it
SQL> select as.dname from dept as order by as.dname;
select as.dname from dept as order by as.dname
ERROR at line 1:
ORA-00936: missing expression
SQL> select "as".dname from dept "as" order by "as".dname;