This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Jan 6, 2011 11:46 AM by CharlesHooper RSS

aargh. "sql comand not properly ended" - something wrong with my join....

543686 Newbie
Currently Being Moderated
select *
from table1 t1
join table2 t2 on t1.pkid = t2.pkid

Those aren't my actual table names, but that structure suffices to generate my error. Clearly I don't understand join syntax. After googling I tried out various permutations, putting the join condition in parens, prefixing the table names with the schema, and stuff like that. No change - still get ora00933.

The only thing that worked was comma-ing the tables, and putting the join condition in a where clause. That won't work for me in the end because the full query I need has a lot more joins and it'll look awful like that.

Little help on just a simple inner join?

thanks!

Edited by: sherifffruitfly on Jan 4, 2011 10:18 AM
  • 1. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    582561 Newbie
    Currently Being Moderated
    both table has common columns and how oracle will detect to fetch which column from which table. So use tablename.columnname for common column
  • 2. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    543686 Newbie
    Currently Being Moderated
    Doesn't matter what I put in for the select cols. I could just say select t1.field1 from blahblahblah and I get the exact same error. I'm failing with the join - when I just do single table (either table), it/they work fine. I get the error when my join syntax as per above is introduced. Wtf am I doing wrong? grrrr. :(

    Edited by: sherifffruitfly on Jan 4, 2011 10:32 AM
  • 3. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    MaximDemenko Pro
    Currently Being Moderated
    Your syntax is fine - could you post the output (if your client is sqlplus)
    define _SQLPLUS_RELEASE
    Another guess - may be your table/column names are reserved words, so the parser can't properly parse your query - could you provide exactly the query text and the error message?

    Best regards

    Maxim
  • 4. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    543686 Newbie
    Currently Being Moderated
    DEFINE SQLPLUSRELEASE = "1002000300" (CHAR)

    And I'm running the code from toad 10.6.0.42, if it matters.

    It would be nice knowing that the syntax is ok, except that the #$%$# thing isn't working. lols
  • 5. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    odie_63 Guru
    Currently Being Moderated
    Can you also give your database version?
    SELECT * FROM v$version;
  • 6. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    543686 Newbie
    Currently Being Moderated
    Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    PL/SQL Release 8.1.7.0.0 - Production
    CORE     8.1.7.0.0     Production
    TNS for Solaris: Version 8.1.7.0.0 - Production
    NLSRTL Version 3.4.1.0.0 - Production

    5 rows selected.

    I'm a dotnet/mssql guy, don't know much about oracle (just had something plopped onto my plate) - but I get the feeling this is a really really old version I'm dealing with here. lols
  • 7. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    John Spencer Oracle ACE
    Currently Being Moderated
    The ANSI join syntax was introduced in Oracle in version 9.1. Your version has been out of support for at about 10 years, and the current version of Oracle is 11.2.

    Unless you can upgrade to something from this century, you need to do the joins in the where clause.

    John
  • 8. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    MaximDemenko Pro
    Currently Being Moderated
    Well, now it is obvious - ansi join syntax was introduced in oracle 9i, so your database simply not support this syntax.

    Best regards

    Maxim
  • 9. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    543686 Newbie
    Currently Being Moderated
    wtf? People used to actually do joins some other way? (I feel like when I was a kid, and asking my mom what they did before there were cash machines lols)

    Sigh so be it - into ugly-code world. Thanks for the help diagnosing my issue folks!

    edit: I don't suppose there are any normal-query-sytax-to-freakishly-old-syntax converters out there?

    Edited by: sherifffruitfly on Jan 4, 2011 11:18 AM
  • 10. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    John Spencer Oracle ACE
    Currently Being Moderated
    Personally, I'm looking for a freakishly-new-syntax-to-normal-query-sytax converter :-)

    Joins in the where clause are perfectly normal, and work in any dialect of sql that I have ever used.

    John
  • 11. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    CharlesHooper Expert
    Currently Being Moderated
    sherifffruitfly wrote:
    edit: I don't suppose there are any normal-query-sytax-to-freakishly-old-syntax converters out there?
    Install a copy of Oracle XE (the free version of Oracle Database), enable a 10053 trace, execute your query, and then look in the 10053 trace file to see how the optimizer converts the submitted ANSI join syntax into Oracle specific join syntax. Yes, every submitted ANSI query (on Oracle 9i and above) is translated into Oracle join syntax by the optimizer with the possible exception of full outer joins. You can see an example of that method here:
    http://hoopercharles.wordpress.com/2010/12/26/feeling-ansi-about-oracle-join-syntax/

    If it makes you feel any better about learning Oracle specific join syntax, you can read about some of the ANSI related bugs that affect Oracle Database 10.2.0.4 and 11.2.0.1 here:
    http://hoopercharles.wordpress.com/2010/12/30/ansi-full-outer-join-ready-or-not/

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 12. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    543686 Newbie
    Currently Being Moderated
    Charles Hooper wrote:
    sherifffruitfly wrote:
    edit: I don't suppose there are any normal-query-sytax-to-freakishly-old-syntax converters out there?
    Install a copy of Oracle XE (the free version of Oracle Database), enable a 10053 trace, execute your query, and then look in the 10053 trace file to see how the optimizer converts the submitted ANSI join syntax into Oracle specific join syntax.
    Interesting idea. Obviously I won't have the tables on the freelocaloracle install, but I suppose that the query will at least get far enough through the execution process to spit out the old school version in the trace dump?
  • 13. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    543686 Newbie
    Currently Being Moderated
    John Spencer wrote:
    Personally, I'm looking for a freakishly-new-syntax-to-normal-query-sytax converter :-)

    Joins in the where clause are perfectly normal, and work in any dialect of sql that I have ever used.

    John
    And I suppose you knew the Wright brothers at Kittyhawk too? :P

    Guess I always thought of FROM as being the "core" recordset I wanted to play with, and the WHERE as being for "filtering" that core set - like where application parameters would typically be passed to. But I guess that's the stuff religious computer wars are made of - no need to go there. :)
  • 14. Re: aargh. "sql comand not properly ended" - something wrong with my join....
    CharlesHooper Expert
    Currently Being Moderated
    sherifffruitfly wrote:
    Interesting idea. Obviously I won't have the tables on the freelocaloracle install, but I suppose that the query will at least get far enough through the execution process to spit out the old school version in the trace dump?
    The table definitions must exist in the database for the 10053 trace method to work (10053 files are only written on a hard parse).

    Since you are running Oracle 8i, you can used the exp command with ROWS=N specified to export the table definitions without the table data:
    http://download.oracle.com/docs/cd/F49540_01/DOC/server.815/a67792/ch01.htm#34709

    On Oracle XE you could then import the table definitions using the imp utility:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#i1021478

    What is possibly interesting is to see in a 10053 trace file how Oracle's query optimizer rewrites the SQL statement into equivalent, yet more efficient SQL statements, even when SQL statements are submitted using Oracle specific join syntax - so you should see several examples in a single 10053 trace file of how the ANSI SQL statement could be rewritten in Oracle specific join syntax. You might consider setting the OPTIMIZER_FEATURES_ENABLE parameter in Oracle XE to '8.1.7' to help control how the final SQL statement is rewritten.

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points