SQL Filtering

[DanNorris2000]

I would like to filter out a rather large list of contracts from a file , some between a range as in 99001 thru 99042 as well as a list of individual contracts , 12 to be exact.

What is the most efficient way to accomplish this?

 

[foxdev]

When you say "filter out", do you mean from a query or remove them from a file?

Probably the simplest would be an SQL SELECT statement; your individual exception list is a bit long, but still possible, such as:

[tt]select * from MyTable ;
where (not between(MyField, 99001, 99042)) ;
and not inlist(MyField, exclude1, exclude2, exclude3...)[/tt]



Robert Bradley

http://www.foxdev.com/

 

[DanNorris2000]

Im filtering out for a query. I mis-stated in that I want to only "include" certain contracts. I seem to be having trouble getting a successful run using

select * from MyTable ;
where (inlist(MyField, contract1, contract2, contract3, and on 8,9,10,11,12,13,14)

Error comes back for too many arguments

 

[foxdev]

It looks like you have an extra open paren, but other than that it should be fine. INLIST() can accept up to 24 parameters.

Robert Bradley

http://www.foxdev.com/

 

[TomasDill]

Correct and the best sample:

select * from MyTable ;
where MyField in ("contract1", "contract2", "contract3&quot ;
and MyField2 in (8,9,10,11,12,13,14)

You can use in list of items for 'in' operator in SQL-Where theoretically unlimited list of values (I hope).



Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softserve.lviv.ua

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[LenaS]

I obviously have too many contracts to compare against. Would it be better to maybe have a table with all the contracts I wish to include and have that as my Parent file?
I think the trouble comes in when I need to include a range of contracts as well as individual contracts and I have too many (AND)'s and (OR)'s
where contracts between(1,50)
or contracts between(999 thru contract 9999)
or contract inlist(90234,90236,90239,.....)
If I knew that the inlist could handle as many as I could throw at it I would do it that way.

 

[DanNorris2000]

My apologies, I type the response above on anothers users PC

I obviously have too many contracts to compare against. Would it be better to maybe have a table with all the contracts I wish to include and have that as my Parent file?
I think the trouble comes in when I need to include a range of contracts as well as individual contracts and I have too many (AND)'s and (OR)'s
where contracts between(1,50)
or contracts between(999 thru contract 9999)
or contract inlist(90234,90236,90239,.....)
If I knew that the inlist could handle as many as I could throw at it I would do it that way.