Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Posts
    11

    Unanswered: ORA-00933:SQL command not properly ended

    There are the tables one and two :

    Table = One
    PID AMOUNT1
    1 10
    1 20
    2 10
    2 10
    3 30

    Table = Two
    PID AMOUNT2
    1 20
    1 50
    2 30
    2 20

    I want the output :

    PID AMOUNT1 AMOUNT2
    1 30 70
    2 20 50
    3 30



    This is the query I tried :


    SELECT o.pid, o.amount1, t.amount2
    FROM (SELECT pid , SUM(amount1) AS "Amount1OfSum" FROM one o group by pid ) as o

    LEFT OUTER JOIN ( SELECT pid, SUM(amount2) AS "Amount2OfSum" FROM two t group by pid) as t
    ON t.pid = o.pid

    and getting ORA -00933 SQL command not properly ended Vendor code 933 Error at Line:27 Column:78

    I searched a lot how to fix this but no luck.....Plz let me know where I am wrong?

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by jason_bourne View Post
    I searched a lot how to fix this but no luck.....Plz let me know where I am wrong?
    Using AS keyword for defining table alias. Although it is the (optional) part of ANSI SQL, Oracle does not support it at all.

    For your next Oracle SQL experience, you may consult SQL Reference book, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/

  3. #3
    Join Date
    Aug 2011
    Posts
    11

    Reply

    Thanks for your reply . Then how can we fix this ?

    If I remove the AS keyword, then I am getting an error " ORA-00904: "t"."amount2":INVALID IDENTIFIER .........so i placed an "(" and ")"

    SELECT o.pid, o.amount1, t.amount2
    FROM ((SELECT pid , SUM(amount1) AS "Amount1OfSum" FROM one o group by pid ) o

    LEFT OUTER JOIN ( SELECT pid, SUM(amount2) AS "Amount2OfSum" FROM two t group by pid) t
    ON t.pid = o.pid)

    and after putting the round brackets I am getting ORA-00907 :Missing right parenthesis.........

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by jason_bourne View Post
    If I remove the AS keyword, then I am getting an error " ORA-00904: "t"."amount2":INVALID IDENTIFIER
    It happens because that SQL contained more than one error; after fixing one the compiler found another one.

    Note the difference between "Amount2OfSum" (alias in the inner SELECT) and "AMOUNT2" used in the main SELECT list. They should apparently be same.

    By the way, Oracle identifiers are case insensitive (uppercase) unless placed between double quotes.

  5. #5
    Join Date
    Aug 2011
    Posts
    11
    Quote Originally Posted by flyboy View Post
    It happens because that SQL contained more than one error; after fixing one the compiler found another one.

    Note the difference between "Amount2OfSum" (alias in the inner SELECT) and "AMOUNT2" used in the main SELECT list. They should apparently be same.

    By the way, Oracle identifiers are case insensitive (uppercase) unless placed between double quotes.

    with reference to what you said, where do i need to change the code in the SQL ?


    Could you please correct the query which I have written and post the right query ?
    Last edited by jason_bourne; 08-08-11 at 09:26.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by jason_bourne View Post
    with reference to what you said, where do i need to change the code in the SQL ?

    Could you please correct the query which I have written and post the right query ?
    It depends on how exactly should be named the column in the main query. So, it may be any of these:
    Code:
    SELECT o.pid, o."Amount1OfSum", t."Amount2OfSum"
    FROM (SELECT pid , SUM(amount1) AS "Amount1OfSum" FROM one o group by pid ) o
    LEFT OUTER JOIN ( SELECT pid, SUM(amount2) AS "Amount2OfSum" FROM two t group by pid) t
    ON t.pid = o.pid
    
    SELECT o.pid, o.amount1, t.amount2
    FROM (SELECT pid , SUM(amount1) AS amount1 FROM one o group by pid ) o
    LEFT OUTER JOIN ( SELECT pid, SUM(amount2) AS amount2 FROM two t group by pid) t
    ON t.pid = o.pid
    
    SELECT o.pid, o.whatever1, t.whatever2
    FROM (SELECT pid , SUM(amount1) AS whatever1 FROM one o group by pid ) o
    LEFT OUTER JOIN ( SELECT pid, SUM(amount2) AS whatever2 FROM two t group by pid) t
    ON t.pid = o.pid
    e.t.c.

    But, as you did not post SQL statements for creating all involved tables (also called test case), I cannot guarantee there are none other errors.

  7. #7
    Join Date
    Aug 2011
    Posts
    11
    Quote Originally Posted by flyboy View Post
    It depends on how exactly should be named the column in the main query. So, it may be any of these:
    Code:
    SELECT o.pid, o."Amount1OfSum", t."Amount2OfSum"
    FROM (SELECT pid , SUM(amount1) AS "Amount1OfSum" FROM one o group by pid ) o
    LEFT OUTER JOIN ( SELECT pid, SUM(amount2) AS "Amount2OfSum" FROM two t group by pid) t
    ON t.pid = o.pid
    
    SELECT o.pid, o.amount1, t.amount2
    FROM (SELECT pid , SUM(amount1) AS amount1 FROM one o group by pid ) o
    LEFT OUTER JOIN ( SELECT pid, SUM(amount2) AS amount2 FROM two t group by pid) t
    ON t.pid = o.pid
    
    SELECT o.pid, o.whatever1, t.whatever2
    FROM (SELECT pid , SUM(amount1) AS whatever1 FROM one o group by pid ) o
    LEFT OUTER JOIN ( SELECT pid, SUM(amount2) AS whatever2 FROM two t group by pid) t
    ON t.pid = o.pid
    e.t.c.

    But, as you did not post SQL statements for creating all involved tables (also called test case), I cannot guarantee there are none other errors.
    Bingo...It worked......Thanks a lot...............I understood the reason behind the error........It was helpful.........Thanks again

Posting Permissions

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