Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: ORA-00928: missing SELECT keyword

    Hi

    I have created a question in Access and then copied the SQL question from it. When I try to run it in eg SQL plus I get an error.From the error I understand that there must be one more Select statement but I can't figure out where or why.

    I am trying to access an Oracle DB so I guess I must change the question but I am not sure how.


    MC_STOREROOM.STOROI, MC_INVTRANS.TRNTYP
    FROM ((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN MC_PODEL ON MC_POLINE.POLNOI = MC_PODEL.POLINE_OI) INNER JOIN MC_INVTRANS ON MC_PODEL.PODELOI = MC_INVTRANS.PODEL_OI) INNER JOIN MC_EMPLOYEE ON MC_INVTRANS.AUDT_USER_OI = MC_EMPLOYEE.EMPOI) INNER JOIN MC_PCITEM ON MC_POLINE.PCITEM_OI = MC_PCITEM.PCITOI) LEFT JOIN MC_STOREROOM ON MC_INVTRANS.STOR_OI = MC_STOREROOM.STOROI) INNER JOIN MC_EMPLOYEE AS MC_EMPLOYEE_1 ON MC_POSUM.BUY_OI = MC_EMPLOYEE_1.EMPOI WHERE (((MC_EMPLOYEE_1.AENM) Not Like [MC_EMPLOYEE.AENM]) AND ((MC_STOREROOM.STOROI) Not Like 32050) AND ((MC_INVTRANS.TRNTYP) Like 5));

  2. #2
    Join Date
    Mar 2004
    Posts
    25

    Re: ORA-00928: missing SELECT keyword

    Originally posted by Chrillef
    Hi

    I have created a question in Access and then copied the SQL question from it. When I try to run it in eg SQL plus I get an error.From the error I understand that there must be one more Select statement but I can't figure out where or why.

    I am trying to access an Oracle DB so I guess I must change the question but I am not sure how.


    MC_STOREROOM.STOROI, MC_INVTRANS.TRNTYP
    FROM ((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN MC_PODEL ON MC_POLINE.POLNOI = MC_PODEL.POLINE_OI) INNER JOIN MC_INVTRANS ON MC_PODEL.PODELOI = MC_INVTRANS.PODEL_OI) INNER JOIN MC_EMPLOYEE ON MC_INVTRANS.AUDT_USER_OI = MC_EMPLOYEE.EMPOI) INNER JOIN MC_PCITEM ON MC_POLINE.PCITEM_OI = MC_PCITEM.PCITOI) LEFT JOIN MC_STOREROOM ON MC_INVTRANS.STOR_OI = MC_STOREROOM.STOROI) INNER JOIN MC_EMPLOYEE AS MC_EMPLOYEE_1 ON MC_POSUM.BUY_OI = MC_EMPLOYEE_1.EMPOI WHERE (((MC_EMPLOYEE_1.AENM) Not Like [MC_EMPLOYEE.AENM]) AND ((MC_STOREROOM.STOROI) Not Like 32050) AND ((MC_INVTRANS.TRNTYP) Like 5));
    Okay, I may be missing something obvious, but it looks to me like you're just missing the SELECT from the very beginning of the query...

  3. #3
    Join Date
    Apr 2004
    Posts
    2

    I missed a couple of rows...

    ..while pasting.

    My question actually looks like this:

    SELECT MC_POSUM.ID, MC_POLINE.ID, MC_INVTRANS.TQTY_AMT, MC_PCITEM.ITID, MC_INVTRANS.PRSD_DTTM, MC_EMPLOYEE.AENM, MC_EMPLOYEE_1.AENM, MC_STOREROOM.STOREID, MC_STOREROOM.STOROI, MC_INVTRANS.TRNTYP
    FROM ((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN MC_PODEL ON MC_POLINE.POLNOI = MC_PODEL.POLINE_OI) INNER JOIN MC_INVTRANS ON MC_PODEL.PODELOI = MC_INVTRANS.PODEL_OI) INNER JOIN MC_EMPLOYEE ON MC_INVTRANS.AUDT_USER_OI = MC_EMPLOYEE.EMPOI) INNER JOIN MC_PCITEM ON MC_POLINE.PCITEM_OI = MC_PCITEM.PCITOI) LEFT JOIN MC_STOREROOM ON MC_INVTRANS.STOR_OI = MC_STOREROOM.STOROI) INNER JOIN MC_EMPLOYEE AS MC_EMPLOYEE_1 ON MC_POSUM.BUY_OI = MC_EMPLOYEE_1.EMPOI WHERE (((MC_EMPLOYEE_1.AENM) Not Like [MC_EMPLOYEE.AENM]) AND ((MC_STOREROOM.STOROI) Not Like 32050) AND ((MC_INVTRANS.TRNTYP) Like 5));

  4. #4
    Join Date
    Oct 2003
    Posts
    37

    Re: I missed a couple of rows...

    Hi Chillef

    I don't think it will solve your problem, but I would remove the square brackets from your query (there are a couple in the where clause).

    And then I would probably try a portion of the query (ie one column from one table) to make sure that the syntax from access to sql is okay - I've never actually tried to take an access query and use it in sql.

    Good luck


    Regards

    Keith

  5. #5
    Join Date
    Jan 2004
    Posts
    492

    Re: I missed a couple of rows...

    Also too make sure you are on at least Oracle 9i, because Oracle 8i does not support Inner/Left/Right join syntax.

    If you are on Oracle 8i, an inner join is simply tablea.col1 = tableb.col1.
    The outer joins use a plus sign on the side that is deficient.
    tablea.col1 = tableb.col1 (+)

    That could be your problem. Unfortunately Access writes HORRIBLE SQL. In SQL there is absolutely no need for all those parentheses, it just makes it harder to read! So check your Oracle version, and start there- Also take the advice of breaking it into smaller pieces and adding on one table at a time.

    If you are using Oracle 8i or below, you MUST change the join syntax or you will never get your query to run.

    Also as a side note, learn to use column aliases to improve your code.

    Instead of :

    SELECT TABLEA.COL1, TABLEB.COL2
    FROM TABLEA, TABLEB
    WHERE TABLEA.COL2 = TABLEB.COL5

    do:

    SELECT A.COL1, B.COL2
    FROM TABLEA A, TABLEB B
    WHERE A.COL2 = B.COL5

    The second one is much simpler to read, especially when you use many tables, and they have long names!
    Last edited by ss659; 04-02-04 at 08:11.

  6. #6
    Join Date
    Oct 2003
    Posts
    37

    Re: I missed a couple of rows...

    Good point SS659

    I still use the old format for joining tables.

    Regards

    Keith

  7. #7
    Join Date
    Jan 2004
    Posts
    492

    Re: I missed a couple of rows...

    Yea personally I hate the inner/outer join ANSI syntax. It just involves more coding, and clutters up the query. I dont see the point in naming the table you are inner joining when you still have to name the columns from each table that are getting joined.

    Im glad all of the programs I have to look at still use the old syntax. I'm too set in my own ways to change!

Posting Permissions

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