I have a select query that uses several conditional "or" and "and" statements. Can I use a subselect statement? What is the best method of accomplishing this?
SELECT * FROM ASN.IBMSNAP_APPLYTRAIL WHERE SET_UPDATED>0 OR
OR SET_REWORKED>0 AND STATUS=0 ORDER BY LASTRUN DESC
I want any row that contains status=0 and a value>0 in either of the other 3 fields (set_updated, set_inserted, set_reworked)
A subquery is not necessary. All you need to do is use parentheses to ensure that the conditions are combined exactly the way you want --
SELECT * FROM ASN.IBMSNAP_APPLYTRAIL WHERE ( STATUS=0 ) AND ( SET_UPDATED>0 OR SET_INSERTED>0 OR SET_REWORKED>0 ) ORDER BY LASTRUN DESC
Without parentheses, ANDs always take precedence over ORs. Thus you do have to use at least one set of parentheses. The first set of parentheses above, around STATUS=0, could actually be omitted. Some people feel that as soon as you introduce one set of parentheses, though, you should use as many as necessary to achieve a nice balanced look in the SQL statement -- makes for easier maintenance later.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.