This discussion is archived
6 Replies Latest reply: Dec 19, 2012 2:25 AM by 980299 RSS

database adapter vs sql query

980299 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    Hi,

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

    Arik
  • 2. Re: database adapter vs sql query
    980299 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Did you verified the parameter was assigned correctly and it's being passed to the query?
  • 4. Re: database adapter vs sql query
    980299 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points