Q
Problem solve Get help with specific problems with your technologies, process and projects.

Can I use a subselect statement?

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
SET_INSERTED>0
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)


Hi Mark,

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


This was last published in May 2001

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close