Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    40

    SQL command not properly ended - but i'm sure it's ok :(

    Code:
    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.
    Last edited by gunbilegt; 03-11-08 at 04:02.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,009
    There's no use in DELETING a thread.

    But it would be nice if you'd share the solution with the rest of the community, so that other forum members might benefit from it.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2003
    Posts
    2,821
    Quote Originally Posted by r937
    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...)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat
    Oracle complies with the ANSI standard here, so yes the ORDER BY should come after the HAVING.
    i was kidding!

    Quote Originally Posted by shammat
    Actually I think Oracle complies a lot more to the ANSI standard than MS SQL and MySQL.
    in a word: no it doesn't

    oracle thinks NULL and the empty string are the same thing -- worst misteak evar

    oracle doesn't allow the keyword AS for aliases

    oracle doesn't support row constructors, and neither does MS SQL -- yes, it's optional, but very handy, this feature

    oracle doesn't support INFORMATION_SCHEMA

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2003
    Posts
    2,821
    Quote Originally Posted by r937
    in a word: no it doesn't
    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
    Agreed.

    But I'm sure there are more queries out there that use string concatination using || than queries needing the INFORMATION_SCHEMA

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,009
    - Oracle doesn't allow the keyword AS for aliases

    - You mean for a table alias, it does allow it for a column alias.
    Unless we fool it
    Code:
    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;
    
    ACCOUNTING
    OPERATIONS
    RESEARCH
    SALES
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •