Results 1 to 15 of 15

Thread: Order by error

  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: Order by error

    A simple query is causing a error

    select * from ( select * from emp_details order by sal )

    Error is : ORA-00907: missing right parenthesis

    What is the problem in this query ?

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Why not make the query :

    select * from emp_details order by sal ;


    Gregg

  3. #3
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    More help

    Originally posted by gbrabham
    Why not make the query :

    select * from emp_details order by sal ;


    Gregg

    Actually I need to use these types of queries for some other things. BUt as this is causing a problem I cant proceed further.

  4. #4
    Join Date
    Mar 2004
    Location
    Concord, MA
    Posts
    27
    there may be two reasons...

    1. you may have mis-spelled some oracle keyword in your sub-query
    or
    2. open the query in the editor and check if actually the right parenthesis is there or not.....
    ~bagchi

  5. #5
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    More help required

    I am already through with pts u raised.

    There is no mis spelling.

    I have seen the query in editor. Right parenthesis is there.

    If I omit order by clause query runs perfectly. But When i include order by This error appears.

  6. #6
    Join Date
    Mar 2004
    Posts
    14
    select * from ( select * from dual order by dummy )
    work perfect
    and are you sure that you need order in inner select as you goin select again from this set - so inner order have no sence.

  7. #7
    Join Date
    Mar 2004
    Location
    Sydney
    Posts
    20
    Now I suspect this is not the case but stray characters after the table name can cause the problem you describe.

    Code:
    SQL> select * from ( select * from dual order by dummy );
    
    D
    -
    X
    
    SQL> select * from ( select * from dual 1order by dummy );
    select * from ( select * from dual 1order by dummy )
                                                    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis
    
    
    SQL> select * from ( select * from dual 0rder by dummy );
    select * from ( select * from dual 0rder by dummy )
                                                    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis

  8. #8
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    which version r u using. hope order by in sub queries work in versions above 8i
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  9. #9
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    More help required

    I am using Version 8i.

    I have tried everything. Checked atleast 20 times . No syntax error. But the problem still persists.

    SQL> select * from (select * from emp_details order by sal desc);
    select * from (select * from emp_details order by sal desc)
    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis

    Emp_details table exists and it is having sal field.

    I hope somebody can help.

  10. #10
    Join Date
    Mar 2004
    Posts
    14
    I use 9i so it's seems to me that problem really in ver of Oracle because there is no errors in select. I send this select to my friend with Oracle 8i
    just for proof, but will have result tommorow and let you know.

    buy the way "select distinct * " have a ordered result too ( side effect ) may be you can use it by some way if you have a UK in this set.

  11. #11
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Oracle 8i ... even though I wouldn't ever write it like this !!!

    select * from (select * from units order by unit_of_measure);

    UNIT_OF_MEASURE DESCRIPTION SCALE
    --------------- ---------------------------------------- ----------
    $ DOLLARS 2
    BX BOX 2
    CS CASE 2
    CTN CARTON 2
    DR DRUM 2
    DZ DOZEN 2
    EA EACH 0
    FT FEET 2
    GL GALLON 2
    GR GRAM 2
    IN INCH 2

    No Problems

    Gregg

  12. #12
    Join Date
    Jan 2004
    Location
    Leiden, The Netherlands
    Posts
    11
    Try

    SQL> select * from v$version;

    to find out your Oracle version.

    examples:

    SQL> select * from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
    PL/SQL Release 8.1.7.4.0 - Production
    CORE 8.1.7.0.0 Production
    TNS for HPUX: Version 8.1.7.4.0 - Production
    NLSRTL Version 3.4.1.0.0 - Production

    5 rows selected.

    or

    SQL> select * from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle7 Server Release 7.3.4.5.0 - Production
    PL/SQL Release 2.3.4.5.0 - Production
    CORE Version 3.5.4.0.0 - Production
    TNS for HPUX: Version 2.3.4.0.0 - Production
    NLSRTL Version 3.2.4.0.0 - Production

    5 rows selected.

    TOP-N queries don't work in Oracle 7.1 !!
    Last edited by rcoerwin; 03-19-04 at 06:45.

  13. #13
    Join Date
    Mar 2004
    Posts
    9
    I use Oracle8i Enterprise Edition Release 8.1.7.4.0 and everything works fine.

  14. #14
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: More help required

    Originally posted by varun_751980
    SQL> select * from (select * from emp_details order by sal desc);
    It's not very relational to have an order in a subquery, because the outer select might return the records in another way. If you want to have an order, put it on the outer select!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  15. #15
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Amazing

    Originally posted by gbrabham
    Oracle 8i ... even though I wouldn't ever write it like this !!!

    select * from (select * from units order by unit_of_measure);

    UNIT_OF_MEASURE DESCRIPTION SCALE
    --------------- ---------------------------------------- ----------
    $ DOLLARS 2
    BX BOX 2
    CS CASE 2
    CTN CARTON 2
    DR DRUM 2
    DZ DOZEN 2
    EA EACH 0
    FT FEET 2
    GL GALLON 2
    GR GRAM 2
    IN INCH 2

    No Problems

    Gregg

    I created a table unit in my schema and tried the query.But the error persists. Is'nt it amazing that it works on ur machine and not mine.


    SQL> select * from (select * from units order by unit_of_measure);
    select * from (select * from units order by unit_of_measure)
    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis

Posting Permissions

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