SQL Where Clause with multiple .....

[mstrcmtr]

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

 

[Mike Lewis]

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:

[tt]SELECT * FROM ActnTbl WHERE Code IN ('0001','0098','0044')[/tt]

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

 

[Mike Lewis]

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:

[tt]SELECT * FROM ActnTbl WHERE Code IN (SELECT Code FROM csrCodes)[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

How do you want to make that shorter?

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

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

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

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.