SQL query available in finder

[neronimo]

Hi,

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.

Eric

[james_foran]

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.