This query works error free however there is a syntax problem which causes the collection of the:
AND(
 groupNames.gnInactive <> 1
 OR shipClass.scInactive
 OR osirHazardCats.inactive <> 1
   )
conditions (starting on the third to last line) to be ignored. I have bit data type fields in each table which are the inactive flags. The query returns a two dimentioanl data set EG:

Biological 2
Chemical 52
Ergonomic 1
Mechanical/Electrical 86
Physical 243
to be defined 3

Which is placed into a graphing object however when I inactivate items they still appear in the result set. I have been looking at this query for a while now and can not seem to make it work.

SELECT osirHazardCats.catName , COUNT(distinct osirReport.osirId) AS incidentNumbers FROM ((((((((((osirReport
LEFT OUTER JOIN originator ON osirReport.osirOrId = originator.orId)
LEFT OUTER JOIN shipClass ON originator.orScId = shipClass.scId)
LEFT OUTER JOIN groups ON shipClass.scId = groups.gScId)
LEFT OUTER JOIN groupNames ON groups.gGnId = groupNames.gnId)
LEFT OUTER JOIN incidents ON incidents.incOsirId = osirReport.osirId)
LEFT OUTER JOIN incidentType ON incidentType.incidentId = incidents.incIncidentId)
LEFT OUTER JOIN osirsCausalFactors ON osirsCausalFactors.ocfOsirId = osirReport.osirId)
LEFT OUTER JOIN causalFactors ON causalFactors.cfId = osirsCausalFactors.ocfCfId)
LEFT OUTER JOIN osirType ON osirReport.osirTypeId = osirType.otId)
LEFT OUTER JOIN osirHazardCats ON osirReport.hazardCatID = osirHazardCats.Id)
WHERE (osirReport.osirSignalDateTime >= '1/1/2006 12:00:00 AM'
AND osirReport.osirSignalDateTime <= '5/28/2006 12:00:00 AM') AND (osirReport.osirTypeId IN (2))
AND osirReport.osirHri >= 0 AND osirReport.osirHri <= 20
AND (groupNames.gnInactive <> 1 OR shipClass.scInactive <> 1 OR originator.orInactive <> 1
OR incidentType.incidentInactive <> 1 OR osirHazardCats.inactive <> 1 OR osirHazardCats.id <> 0 OR causalFactors.cfInactive <> 1)
GROUP BY osirHazardCats.catName


TYIA

Wind is your friend
Matt

__________________
Wind is your friend
Matt

There doesn't appear to be any "syntax problem[s]" in the query, as near as I can tell.

If those OR conditions are being ignored, then I would look for NULL problems in the various values being tested.

Since you are using OUTER JOINs, remember that column expressions like groupNames.gnInactive will take on a NULL value even though such a column may be defined as not allowing NULL values, if no row from the groupNames table exists meeting the JOIN condition.

Thus, an expression like groupNames.gnInactive <> 1 may result in an UNKNOWN value instead of just TRUE or FALSE and there you go into the land of three-valued logic...


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Agghh The NULLS. Yes there are some. Would it be correct in saying it would execute properly if there are no nulls in the result set?

I worked around the problem and now have the desired outcome regardless of nulls. There may be a better way? Its a conditional query that gets run as a result of a chart wizard (why so many joins) So instead of the:

AND (groupNames.gnInactive <> 1 OR shipClass.scInactive <> 1 OR...

line I have done the following: Yes the physical table names are hard coded and there is one of these conditions the seven tables - I guess it works so Im happy:

if ((trim(tbleName) = "groupNames") AND (trim(fieldName) = "gnName")) then
   sql = sql & " AND (groupNames.gnName is NOT Null) AND (groupNames.gnInactive <> 1) "
end if

Wind is your friend
Matt

BTW I did try this in my attempt to get the original statement working. I replaced:

AND (groupNames.gnInactive <> 1 OR shipClass.scInactive <> 1 OR originator.orInactive <> 1
OR incidentType.incidentInactive <> 1 OR osirHazardCats.inactive <> 1 OR osirHazardCats.id <> 0 OR causalFactors.cfInactive <> 1)

With:

sql = sql & " AND ((groupNames.gnInactive <> 1) OR (shipClass.scInactive <> 1) OR (originator.orInactive <> 1) OR (incidentType.incidentInactive <> 1) OR (osirHazardCats.inactive <> 1) OR (osirHazardCats.id <> 0) OR (causalFactors.cfInactive <> 1))"

Can you see that making a difference? It didnt seem to.

Wind is your friend
Matt

No. It looks like all you did was insert parentheses around the expressions combined with the OR operators. Since the OR operator has a lower associative precedence than <>, the parentheses are redundant.

Your earlier post is on the right track, testing each column value for a NULL value as well as performing the comparison and taking action accordingly.

You asked in that post if the code would execute correctly if there were no NULLs in the result set. I'm not sure you are using the term 'result set' correctly in the context of your question (since a 'result set' is the, er, result, of executing a SELECT query - we're talking about a WHERE predicate which is executed as the result set is being constructed), but the short answer is yes, if a column value is not null then the comparisons will be 'correct'.

But, given that you are using OUTER JOINs, there is no way to guarantee that the values will never be NULL. They will take on a NULL value if there is no corresponding row that satisfies the JOIN condition. If you know for certain there would always be a corresponding row, then there is no need for an OUTER JOIN; then an INNER JOIN would be preferable.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com