8 Replies Latest reply on Nov 22, 2006 2:13 PM by Rob van Wijk

    ANSI SQL syntax?

    Paulie
      Hi all,

      I have a simple query

      SELECT A.*, B.Dstrct_Code FROM MSF601 A, MSF600 B
      WHERE ALTERNATE_REF LIKE 'PF%'
      AND A.alt_ref_code = B.Equip_No
      AND B.Dstrct_Code = 'ACME';


      which works fine, but I want to convert it to ANSI
      SQL syntax, so I tried


      SELECT A.*, B.Dstrct_Code FROM MSF601 A, MSF600 B
      WHERE ALTERNATE_REF LIKE 'PF%'
      INNER JOIN ON A.alt_ref_code = B.Equip_No
      AND B.Dstrct_Code = 'ACME';


      but I get
      -------------------------
      ERROR at line 3:
      ORA-00933: SQL command not properly ended
      -----------------------

      Could some kind soul explain why?


      Paul...
        • 1. Re: ANSI SQL syntax?
          jeneesh
          This..?
          SELECT A.*, B.Dstrct_Code FROM MSF601 A INNER JOIN
          MSF600 B
          ON A.alt_ref_code = B.Equip_No
          and ALTERNATE_REF LIKE 'PF%'
          AND B.Dstrct_Code = 'ACME';

          Message was edited by:
          jeneesh
          • 2. Re: ANSI SQL syntax?
            castorp
            The INNER JOIN keyword should come before the WHERE clause
            See
            http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2065648
            • 3. Re: ANSI SQL syntax?
              Rob van Wijk
              An example that looks a lot like your example:
              SQL> select dept.*
                2       , emp.ename
                3    from dept, emp
                4   where dept.dname like '%A%'
                5   inner join on dept.deptno = emp.deptno
                6     and emp.sal > 1000
                7  /
              inner join on dept.deptno = emp.deptno
              *
              FOUT in regel 5:
              .ORA-00933: SQL command not properly ended


              SQL> select dept.*
                2       , emp.ename
                3    from dept
                4         inner join emp on dept.deptno = emp.deptno
                5   where dept.dname like '%A%'
                6     and emp.sal > 1000
                7  /

                                              DEPTNO DNAME          LOC           ENAME
              -------------------------------------- -------------- ------------- ----------
                                                  30 SALES          CHICAGO       ALLEN
                                                  30 SALES          CHICAGO       WARD
                                                  20 RESEARCH       DALLAS        JONES
                                                  30 SALES          CHICAGO       MARTIN
                                                  30 SALES          CHICAGO       BLAKE
                                                  10 ACCOUNTING     NEW YORK      CLARK
                                                  20 RESEARCH       DALLAS        SCOTT
                                                  10 ACCOUNTING     NEW YORK      KING
                                                  30 SALES          CHICAGO       TURNER
                                                  20 RESEARCH       DALLAS        ADAMS
                                                  20 RESEARCH       DALLAS        FORD
                                                  10 ACCOUNTING     NEW YORK      MILLER

              12 rijen zijn geselecteerd.
              Regards,
              Rob.
              • 4. Re: ANSI SQL syntax?
                543662
                That error might occur because you are using an Oracle release that not support that ANSI sql. Look this example:

                In Oracle 8i:


                sql >> create table t (id number);

                Table created.

                sql >> create table c (id number);

                Table created.

                sql >> select t.id
                2 from t
                3 inner join c on t.id=c.id;
                inner join c on t.id=c.id
                *
                ERROR at line 3:
                ORA-00933: SQL command not properly ended


                In Oracle 10g:


                sql >> create table t (id number);

                Table created.

                sql >> create table c (id number);

                Table created.

                sql >> select t.id
                2 from t
                3 inner join c on t.id=c.id;

                no rows selected

                Message was edited by:
                Leonardo Horikian
                • 5. Re: ANSI SQL syntax?
                  Rob van Wijk
                  Leonardo,
                  That error is because you are using an Oracle release
                  that not support ANSI.
                  As you can see from my example above (executed on a 9.2.0.7.0 database), the error message can also indicate that the syntax is just invalid, even though the database supports ANSI joins.

                  Regards,
                  Rob.
                  • 6. Re: ANSI SQL syntax?
                    543662
                    Yes, your example is right, but look my example. I execute the same statement in differente databases (8i vs 10g) and as yo can see... in 8i return error but in 10g that no row exists.
                    • 7. Re: ANSI SQL syntax?
                      6363
                      That error is because you are using an Oracle release that not support ANSI.
                      Ah, those were the days, I suppose there is no chance of Oracle desupporting it again.

                      You can certainly tell it was designed by a committee.
                      • 8. Re: ANSI SQL syntax?
                        Rob van Wijk
                        Yes I know, but my point was that you stated

                        "That error is because ..."

                        and because there might be other reasons for this error message to appear, it should be written as:

                        "That error might occur because ..."

                        or at least something less absolute than "is".

                        Well, never mind, I hope the original poster now realizes that there are two reasons why he might get the error message.

                        Regards,
                        Rob.