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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close