RTC Query Logic
Hello,
Was wondering if anyone can explain the RTC query logic to me.
How I view it is that the outer most query is run first, and then passes to the next inner most query, and so on and so forth until it arrives at the inner most query. When I say 'passes' I do mean it passes the results and not ANDing or ORing it's results with it's adjacent most inner query. However I am not getting the results I expect.
For example...
Outer AND passes to Outer+1 OR and so on until it reaches the innermost AND.
So...
AND -> OR -> AND -> OR -> AND = Final Result. However based on my conditions I am seeing tuples that should not be there based on my logic.
The 'nesting' of windows seems to indicate this logic or vice versa (Inside first then pass results going out).
Thanks for the help,
Adam
2 answers
I don't know why you made it so complicated. RTC will translate the query to an SQL statement and execute it. And that's it. How the SQL statement is executed depending on the database vendor, but the result should be the same.
Your sample query should generate an SQL statement as shown below (I have made the conditions at the levels exactly matching your screenshot).
select distinct t1.ITEM_ID, t1.STATE_ID, t1.ID
from MODEL.WORK_ITEM t1 left outer join MODEL.CATEGORY t3
on (t1.CATEGORY_ITEM_ID = t3.ITEM_ID)
where ((t1.PROJECT_AREA_ITEM_ID = ?) and t3.INTERNAL_CATEGORY_ID like ?
and (t1.CREATOR_ITEM_ID in(?, ?, ?)
or ((t1.CREATION_DATE >= ?) and (t1.CREATION_DATE <= ?)
and ((t1.CREATOR_ITEM_ID = ?)
or (t1.CREATOR_ITEM_ID = ?))))) order by t1.ID desc
Donald,
Thanks for the reply, I had a picture in the post but apparently I need so many points to be able to post images.
Anyways I was able to determine the solution.
It doesn't pass results to other 'filters' as I was thinking. Each condition queries everything (logically, hopefully not in reality) a tests against it's condition. Depending on what box it is in it will either AND or OR it with other conditions in the box, or with the results of another box containing within itself.
Comments
Donald Nong
Mar 23 '17, 5:32 p.m.All I know is that full text search will always be executed first. The rest, they should be constructed into one single SQL query. I don't see any passing happening.
Can you give a more detailed example to explain your logic? Say ((Summary contains "test") AND (Type is Defect)), or something like that.
Donald Nong
Apr 03 '17, 2:48 a.m.Not realizing that there is a screen shot. Attached here:
