If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > SQL command not properly ended - but i'm sure it's ok :(

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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 03:02.
Reply With Quote
  #2 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,994
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.
Reply With Quote
  #3 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,800
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...)
Reply With Quote
  #5 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,800
Quote:
Originally Posted by r937
in a word: no it doesn't
Well, no database fully supports the full standard

Quote:
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...

Quote:
oracle doesn't allow the keyword AS for aliases
You mean for a table alias, it does allow it for a column alias.

Quote:
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.

Quote:
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
Reply With Quote
  #7 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,994
Quote:
- 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>
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On