No announcement yet.

SQL query available in finder

  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL query available in finder


    I have a query that I need to make available for users to run. I can't create it using the UI as I need to use the same field twice in my criteria (I want orders with a remark of "200" but not "*AC" or "*AP" or "*AE" or "SPE") and OTM only allows a field to be used once.

    I have created the query in SQL:

    SELECT orr1.order_release_gid
    FROM order_release_remark orr1, order_release_remark orr2
    WHERE orr1.order_release_gid = orr2.order_release_gid
    AND orr1.remark_text NOT IN ('*AC','*AP','*AE','SPE')
    AND orr1.remark_text = '200'
    AND orr1.insert_date > to_date ('2008-10-05','YY-MM-DD')
    AND orr1.insert_date < to_date ('2008-10-07','YY-MM-DD')
    order by orr1.order_release_gid

    I have created the query in OTM using "Order Release" as the Object Type ID and did not tick the "Use in Finder" so I could paste the above code in the SQL fields and saved it. I then edited it and ticked "Use in Finder" to make it available to users and saved.

    However, when I run it in OTM I get the maximum 1000 results instead of the 300 or so when I run it directly on the database, so I can only assume that my code is ignored and it is returning all Order Releases.

    Does anyone have any idea how I could let users run this query?

    As a bonus, it would also be great if they could modify the date range fairly easily although I may be able to get away with a fixed number of days in the past based on the current date.

    Any help would be much appreciated.


  • #2
    Re: SQL query available in finder

    You must leave "Use in Finder" un-checked. This is not used for what you would expect in OTM. It just allows you to add the Direct SQL to the query.

    The SAVED_QUERY table contains another field that is important. That is the QUERY_NAME. This will determine in which screen set the query will be available.

    Let me know if you have any luck.
    James Foran
    Toll Global Information Services