SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 AND/OR in WHERE		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lumbago
Norsk Yak Master

Norway
3241 Posts
Posted - 10/03/2012 :  09:34:23  Show Profile  Reply with Quote
I was just asked to explain a piece of code and I usually don't have a problem with that sort of thing but this one had me wondering! Somebody mad the query using the query designer and it's a complete mess but I've broken things down a bit and have come to this final piece of code that I can't really get my head around:
...
WHERE interview.id is null
and (case.case_item is null or case.case_item = 'dbv')
or (interview.id is null)
and case.case_item in ('dub', 'dim')
and case.interview_office is null
and case.til = -1
I have obfuscated the table/column names but it is this OR in combination with the case.case_item's that really has me confused and I can't seem to get the presedence of things. Can any of you guys rewrite this thing in to proper a more proper where-clause that is actually understandable??

- Lumbago
My blog-> http://thefirstsql.com

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/03/2012 :  09:50:50  Show Profile  Reply with Quote
Just by observation, it seems to be the following, but more often than not, I don't trust my observations without a lot of testing.
WHERE
	interview.id IS NULL
	AND
	( 	
		case.case_item IS NULL 
		OR 
		case.case_item = 'dbv'
		OR
		(
			case.case_item IN ('dub', 'dim') 
			AND 
			case.interview_office IS NULL 
			AND 
			case.til = -1
		)
	)
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3241 Posts
Posted - 10/04/2012 :  04:28:18  Show Profile  Reply with Quote
That is just awesome...right on the money! Thank you so much

I'm not so sure that the logic behind this was intentional or not but nevertheless you decoded it perfectly. I don't think I would have gotten there at all actually hehe...

- Lumbago
My blog-> http://thefirstsql.com
Edited by - Lumbago on 10/04/2012 04:29:01
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/04/2012 :  07:07:05  Show Profile  Reply with Quote
quote:
I don't think I would have gotten there at all actually
You are being way too modest!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000