Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 25th, 2006, 11:21 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default Please check my where condition

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
Reply With Quote
  #2 (permalink)  
Old May 26th, 2006, 06:49 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old May 26th, 2006, 08:15 AM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
Reply With Quote
  #4 (permalink)  
Old May 26th, 2006, 08:18 AM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
Reply With Quote
  #5 (permalink)  
Old May 26th, 2006, 08:52 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:
Can you see that making a difference?
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to check programmatically check the password? thomaskelly ASP.NET 1.0 and 1.1 Basics 1 May 16th, 2008 08:49 PM
Connect to VSS check-in Check-out Programatically rhd110 General .NET 6 August 12th, 2007 07:46 AM
or condition kgoldvas XSLT 1 July 31st, 2007 03:44 AM
Help 'Check ListView' vs 'Check ListBox' MikeY C# 1 February 24th, 2005 02:20 PM



All times are GMT -4. The time now is 07:30 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.