I am trying to create a query and don’t see that the SQL is being generated correctly. Am I missing something???
The conditions look like this
service type = 6
and cpt = 99058
or service type = 2
and cpt between 10000 and 69999
or service type = 6
and cpt between 99201 and 99456
SQL should generate
where ((servicetype = 6 and cpt = 99058)
or (service type = 2 and (cpt between 10000 and 69999)) or (service type = 6 and (cpt between 99201 and 99456)))
instead SQL being generated is
where (service type = 6 and cpt = 99058
or service type = 2 and cpt between 10000 and 69999 or service type = 6 and cpt between 99201 and 99456)
Seems like this won’t bring back what I need since it’s not grouping it correctly.
In a message dated 00-03-20 10:37:59 EST, you write:
[ snip ]
SQL should generate
where ((servicetype = 6 and cpt = 99058)
or (service type = 2 and (cpt between 10000 and 69999)) or (service type = 6 and (cpt between 99201 and 99456)))
instead SQL being generated is
where (service type = 6 and cpt = 99058
or service type = 2 and cpt between 10000 and 69999 or service type = 6 and cpt between 99201 and 99456)
Seems like this won’t bring back what I need since it’s not grouping it correctly.
Is there something I’m not seeing???
I wish they would not do this, but they do. The SQL that you see is correct. What BusObj is doing is taking a short cut to the final SQL program. The short cut is this:
AND takes priority over OR
In other words:
this condition OR that condition AND the other condition
properly evaluates in exactly the same way as
this condition OR (that condition AND the other condition)
If you review your SQL, the extra parens that you are looking for are not strictly required, and therefore do not appear. It does, however, make things a bit more confusing.
Have you bracketed the conditions correctly in the query panel? You can force BO to bracket conditions by right mouse and shift right/left. This should graphically represent the WHERE clause.