4 Replies Latest reply: Dec 6, 2012 9:57 AM by LPS RSS

    SQL error with trying to select rows

    978241
      For each order, I am trying to show the total dollar amount of the purchase and the customer name.

      SELECT a.MOrder, c.Customer_Name, SUM(a.Item_qty*i.Item_price) AS Total_Price_of_Order
      FROM Orders_Items a, Items i, Customers c
      GROUP BY a.Customer_Name;

      and I get a invalid Identifier error???? PLEASE HELP
        • 1. Re: SQL error with trying to select rows
          Solomon Yakobson
          SELECT  a.MOrder,
                  c.Customer_Name,
                  SUM(a.Item_qty*i.Item_price) AS Total_Price_of_Order
            FROM  Orders_Items a,
                  Items i,
                  Customers c
            WHERE -- is missing
            GROUP BY a.MOrder, -- is missing
                     a.Customer_Name;
          SY.
          • 2. Re: SQL error with trying to select rows
            Hoek
            Welcome to the forum.

            Shouldn't you JOIN those tables? There's no predicate/WHERE-clause in your query....?
            Also, you should GROUP BY on both a.MOrder and c.Customer_Name.
            Finally, you made a typo:
            GROUP BY a.Customer_Name;
            a. Customer name is not a valid identifier, you used the wrong table alias.
            • 3. Re: SQL error with trying to select rows
              Frank Kulash
              Hi,

              Welcome to the forum!

              Whenever you have a problem, please post a complete test script so that the people who want to help you can re-create the problem and test their ideas. Include a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
              Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
              Simplify the problem as much as possible. Remove all tables and columns that play no role in this problem.
              Always say which version of Oracle you're using (for example, 11.2.0.2.0).
              See the forum FAQ {message:id=9360002}
              975238 wrote:
              For each order, I am trying to show the total dollar amount of the purchase and the customer name.

              SELECT a.MOrder, c.Customer_Name, SUM(a.Item_qty*i.Item_price) AS Total_Price_of_Order
              FROM Orders_Items a, Items i, Customers c
              GROUP BY a.Customer_Name;

              and I get a invalid Identifier error???? PLEASE HELP
              Post the complete error message, including line numbers.

              "Invalid identifier" usually means that you mentioned a column (or a table, or something like that) which doesn't exist. It could be because the name was mis-spelled, or you could have forgotten to define an alias. If I had CREATE TABLE statements for your tables, I could tell you exactly.

              Once you get that problem solved, you'll get a different error, "ORA-00979: not a GROUP BY expression", because you're referencing a.morder and c.customer_name in the SELECT clause, but neither of them is in the GROUP BY clause. Perhaps the table alias for customer_name needs to be the same (either a. or c.) in both places; it depends on your data, and what results you want.

              Chances are, you also need some join conditions. That won't cause an error, however, just bad results.
              • 4. Re: SQL error with trying to select rows
                LPS
                SQL Join Basic is To Join N table u need N -1 join required......

                here its there is no where or on clause to join the table ....or its going cartesian product.......

                what is the ORAXXXX error thrown....?

                Need more clarity on this.....