×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL Where Clause with multiple .....

SQL Where Clause with multiple .....

SQL Where Clause with multiple .....

(OP)
I want to learn about Usage of SQL Where Clause

Select * From ActnsTbl where Code = '0001'

Is there anyway to call SQL statement for the following

Select * From ActnsTbl where inlist(Code,'0001','0098','0044',....)

Problem
want to get 10 Accounts e.g. for this used inlist But any other way for where clause can be shorten for above condition in SQL statement

RE: SQL Where Clause with multiple .....

Well, given that you need to test for ten explicit values, there's no way of shortening the command. The code you posted will work if you are querying VFP tables. If you are running against a SQL back-end, you should do this instead:

SELECT * FROM ActnTbl WHERE Code IN ('0001','0098','0044')

This will also work against VFP tables, and has the (small) advantage that is is standard SQL rather than VFP-specific.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: SQL Where Clause with multiple .....

Another approach - one that would be suitable if you have a much larger number of codes to test - would be to hold the relevant codes in their own cursor, and to reference that cursor in your WHERE clause.

For example, if csrCodes is a cursor containing the codes that you want to select, you could do this:

SELECT * FROM ActnTbl WHERE Code IN (SELECT Code FROM csrCodes)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: SQL Where Clause with multiple .....

How do you want to make that shorter?

Indeed this works with the VFP Inlist() function, you can also use SQLs IN clause:

CODE

WHERE Code in ('0001','0098','0044',....) 

Then you can go back to VFP operators and make that shorter via

CODE

WHERE Code $ '0001,0098,0044,....' 

But shorter code isn't necessarily faster. The last clause using $ doesn't and can't make use of an index on the code field.

If you have those codes picked in a cursor you can INNER JOIN with it.

Bye, Olaf.



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close