6 Replies Latest reply: Dec 19, 2012 4:25 AM by 980299 RSS

    database adapter vs sql query

    980299
      Hi all, I am developing a small SOA application using a synch BPEL and Database adapter. But I have some problems getting the database results this is the query

      SELECT t1.Location, t1.ID, t2.quantity, t1.Name
      FROM tblitem t1 JOIN tblorderitem t2 ON (t1.id = t2.itemid)
      JOIN tblorder t3 ON (t2.orderid = t3.ID)
      WHERE (t3.id = #Order_id)
      ORDER BY t1.Location

      which get a sorted list according to the Order_id parameter, this query works perfectly on sqlplus, but not in database adapter, Anyone knows why?
        • 1. Re: database adapter vs sql query
          Arik
          Hi,

          What is the problem? Compilation, runtime error, empty records?

          Arik
          • 2. Re: database adapter vs sql query
            980299
            Well maybe I forgot some information in the last post. I am performing an operation in the tables using Select and I am using JDeveloper 11.1.6.

            The query works but it's not returning the records according Where clause, which contain the parameter. Instead the query is returning all records from the database.
            • 3. Re: database adapter vs sql query
              Arik
              Did you verified the parameter was assigned correctly and it's being passed to the query?
              • 4. Re: database adapter vs sql query
                980299
                Yes I did, I checked in the process flow that the parameter has been assigned correctly. Any suggestions?
                • 5. Re: database adapter vs sql query
                  Arik
                  Strange...
                  I would say try the Pure SQL option with the original query (not the one created by the wizard). See if that solve your problem

                  Arik
                  • 6. Re: database adapter vs sql query
                    980299
                    Hi Arik, I have managed to solve the problem. I don't actually understand why, but it is now solved. I am going to explain how I fixed the problem and I hope you or someone else can understand why.

                    I am using three tables each one contains this information

                    tblItem
                    ID NUMBER
                    Location NUMBER

                    tblOrderItem
                    ID NUMBER
                    ItemID NUMBER
                    OrderID NUMBER
                    Quantity NUMBER

                    tblOrder
                    ID NUMBER

                    And I changed the previous query to this

                    SELECT t1.Location, t1.ID, t2.quantity, t1.Name
                    FROM tblitem t1 JOIN tblorderitem t2 ON (t1.id = t2.itemid)
                    WHERE (t2.OrderID = #Order_id)
                    ORDER BY t1.Location

                    I think that I had redundant information adding tblOrder which is filtered by OrderID, when that field is already in tblOrderItem. I don't think this matters, because I can match as many tables as I want to make a bigger query. Anyway, If you know the answer, please let me know.

                    Thanks a lot.
                    Jaume.