OR Operator 3

[campbere]

I am having problems with the or operator I tried running the following query but the computer just hung up. Can someone help?


select count(distinct jit.cd)
from tracker t, control c
where t.issn = c.issn
and t.current = 'Y'
and t.status = 'Production' or t.flag = 'Y'

 

[carp]

I think your computer may not have hung up - it was probably in the midst of a large cartesian join which can take a long time.
Try rewording your query with something like:

select count(distinct jit.cd)
from tracker t, control c
where t.issn = c.issn
and ( t.current = 'Y'
and t.status = 'Production' or t.flag = 'Y');

You may have to add more parenthesis, depending on exactly which combination of conditions you are trying to find. But the " t.issn = c.issn" should be ANDed separately from the rest of your conditions.

 

[karluk]

I think your where condition is incorrectly stated because of the default precedence of AND vs. OR logical operators. AND is evaluated before OR, so your condition evaluates to

where
(t.issn = c.issn and t.current = 'Y' and t.status = 'Production')
or t.flag = 'Y'

Notice that the t.flag = 'Y' stands by itself, with no qualifying join condition between t.issn and c.issn. The result is a Cartesian product of all rows in c with the rows of t for which t.flag='Y'. It's not surprising that the query hangs - it is probably trying to return millions or billions of rows.

I'm not sure what join condition you intended to write, but a good guess is the following. Try running the query again, this time explicitly supplying parentheses.

where t.issn = c.issn
and t.current = 'Y'
and (t.status = 'Production' or t.flag = 'Y')

 

[nontrad]

You may want to try a way to speed up the process.

Analyze which set of selections result in the fewest number of records such as select * from... where t.status = 'Production' or t.flag = 'Y'. Then generate a query to put these records in a temporary table.

THEN use this temporary table to select based on the rest of your selection criteria. You would be amazed how much faster this can be.