Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Posts
    11

    Question Unanswered: ORA-00923:From keyword not found where expetced

    i am in the process of moving a MS SQL database to Oracle and having some problems with the below code.


    SELECT TOP 5
    PERSONNEL.PNAME AS PNAME,
    sum("SALES_ORDER"."ITEMS_NET") AS SumSales
    FROM
    ("ACCOUNT_MANAGERS" INNER JOIN "SALES_ORDER" ON "SALES_ORDER"."ACCOUNT_REF" = "ACCOUNT_MANAGERS"."ACCOUNT_REF") INNER JOIN PERSONNEL ON "ACCOUNT_MANAGERS".PREF = PERSONNEL.PREF
    WHERE
    "SALES_ORDER"."ORDER_OR_QUOTE" = 'Sales Order'
    GROUP BY
    PERSONNEL.PNAME
    HAVING
    sum("SALES_ORDER"."ITEMS_NET") > 5000

    every time i run it i get the error message ORA-00923:From keyword not found where expetced can anyone help?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps it is not FROM which is causing problems, but TOP (as there's no "TOP" in Oracle).

  3. #3
    Join Date
    Sep 2006
    Posts
    11
    your right if i remove the top it works, how can i do the same in Oracle

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by mathew_wise
    your right if i remove the top it works, how can i do the same in Oracle
    Code:
    SELECT * 
    FROM (
      SELECT PERSONNEL.PNAME,
             sum(SALES_ORDER.ITEMS_NET) AS SumSales
       FROM ACCOUNT_MANAGERS 
         INNER JOIN SALES_ORDER ON SALES_ORDER.ACCOUNT_REF = ACCOUNT_MANAGERS.ACCOUNT_REF 
         INNER JOIN PERSONNEL ON ACCOUNT_MANAGERS.PREF = PERSONNEL.PREF
       WHERE SALES_ORDER.ORDER_OR_QUOTE = 'Sales Order'
       GROUP BY PERSONNEL.PNAME
       HAVING sum(SALES_ORDER.ITEMS_NET) > 5000
    ) 
    WHERE rownum <= 5
    But you have to add an ORDER BY to the inner select because there is not such thing as "the first 5 rows" unless you sort them by something.
    Without the ORDER BY you could actually get a different result each time you run it.

Posting Permissions

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