Solved

SQL "OR" Clause question

Posted on 2012-03-26
5
324 Views
Last Modified: 2012-03-26
Is there a more simple way to do this instead of using the or clause?
SELECT  * from mtytable where (accountNo='1' or accountNo='345' or accountNo='2' or accountNo='4' or accountNo='4000' or ...)

I'm looking for something like
SELECT  * from mtytable where accountNo in ('1', '345', '2','4',...... )


Thanks!
0
Comment
Question by:chrisli
  • 3
5 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 200 total points
ID: 37766908
Yes, exactly as you coded using the "IN ()" construct.
:p
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 37766936
if your account numbers are in fact NUMBERS then you should remove the quotes, otherwise your syntax is correct as is


that would apply to both the OR and the IN versions
0
 

Author Comment

by:chrisli
ID: 37766975
I'm feeling pretty stupid now... But how come this is not returning any results?

DECLARE @accounts VARCHAR(max)
Select @accounts = COALESCE( '''' + LTRIM(RTRIM(AccountNo))+ ''', ' + @accounts,' ''' + LTRIM(RTRIM(AccountNo)) + '''') from mytable where [type]='newAccts'

SELECT     * FROM theothertable WHERE    (accountNo IN (@accounts))
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 37766987
because you are using a variable.  a variable (a single value) and a list of values aren't the same thing.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 37766997
don't bother trying to construct the list,  simply use the query itself as the IN condition


select * from theothertable where accountno in
(select accountno from mytable where [type]='newAccts')
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now