homepage Welcome to WebmasterWorld Guest from 54.196.194.204
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
selecting where list elements are in a range
possible to do in a query or need to loop?
LifeinAsia




msg:1578763
 6:02 pm on Jun 5, 2006 (gmt 0)

I have a MS SQL 2000 table with the following structure- 1st & second columns are integers (with 1st < 2nd), and 3rd column is a character string. Example:
1,5,'Range1'
10,11,'Range2'
13,35,'Range3'

I have a list of numbers that I need to check if they are in one of the ranges in the table, and if so grab the 3rd column.

For example, if the list was "1,6,11,16,21" I would want to return "'Range1','Range2'"
Actuallly, I would be even happier if it just verified that elements "1,11" returned matches.

Is there an easy way to do this within a single query? Or do I have to loop over the list and do a query for each element in the list? The table has about 15,000 records and the lists will run 4,000-8,000 elements. So I would much prefer to figure out some way to do it with just 1 database call instead of 1 call for each element!

 

arran




msg:1578764
 6:36 pm on Jun 5, 2006 (gmt 0)

For example, if the list was "1,6,11,16,21" I would want to return "'Range1','Range2'"

Due to the occurrence of 16 and 21, wouldn't "Range3" also be returned.
If not, could you please explain the logic behind your example/schema.

Thanks,
arran.

LifeinAsia




msg:1578765
 6:41 pm on Jun 5, 2006 (gmt 0)

Yes, sorry about that- it should have been "Range1,Range3"

arran




msg:1578766
 6:45 pm on Jun 5, 2006 (gmt 0)

What about "Range2" given the occurrence of 11?

Sorry to nitpick, i'm just making sure I understand what you're trying to achieve :)

LifeinAsia




msg:1578767
 8:22 pm on Jun 5, 2006 (gmt 0)

I'm trying to achieve a coherent sentence, and it's not working out today. :)

Let's try this again...

If the test list was "1,6,11,16,21" I'd like to get back that "1,11,16,21" were found to be in valid ranges. Or alternatively, that "6" was found not to be within any of the ranges.

Demaestro




msg:1578768
 8:37 pm on Jun 5, 2006 (gmt 0)

Does the list you are wanting to check to see if the elements fall into range live in a table or get passed in as an incoming arg?

LifeinAsia




msg:1578769
 8:46 pm on Jun 5, 2006 (gmt 0)

It could be setup either way. The list is coming from a subset of another query.

Demaestro




msg:1578770
 10:18 pm on Jun 5, 2006 (gmt 0)

LIA I keep trying to write this out and I keep mstalling. So far I am thinking that a case statement is the right track. Although I am think I am stuck where you might be and that is doing it without looping through the values you want to check. I presume you want 1 record per value? Returning soething like:

value range
1 range1
2 range1
6 No Range Found

Type of thing.

I was thinking if you could loop through the logic to dynamically create SQL that you could run you could create a big union statement that would return the data all in one record set.

So something like:

sql = ''
for value_to_check in [1,2,6]:

sql = sql + """
Select
..case
....when
......value_to_check >= (select low_range1 from range_table) and value_to_check <= (select high_range1 from range_table)
....then
......value_to_check as value
......'Range1' as range
....when
......value_to_check >= low_range2 and value_to_check <= high_range2
....then
......value_to_check as value
......'Range2' as range
....when
......value_to_check >= low_range3 and value_to_check <= high_range3
....then
......value_to_check as value
......'Range3' as range
....else
......value_to_check as value
......'No Range Found' as range

**Unless end loop** 'Union All'
"""

Not sure if this is what you are looking for. If not I am willing to take another stab at it with you.

LifeinAsia




msg:1578771
 11:14 pm on Jun 5, 2006 (gmt 0)

Not exactly...

We finally decided to just brute force it and loop through the list. We dumped the data from the table into a temp table (instead of hitting the database table each trip through the loop) and ran some tests. The performance wasn't as bad as we feared, so I guess we'll stick with the brute force method.

Thanks anyway!

Demaestro




msg:1578772
 3:09 pm on Jun 6, 2006 (gmt 0)

No problem, sorry I didn't have more time yesterday to really get my head around it. If you ever go to optmize it I would be up for taking another stab at it.

syber




msg:1578773
 1:31 pm on Jun 8, 2006 (gmt 0)

I think this will work for you:


SELECT num1, (SELECT range
FROM rangetable
WHERE numlist.num1 BETWEEN col1 AND col2)
FROM numlist
WHERE (SELECT range
FROM rangetable
WHERE numlist.num1 BETWEEN col1 AND col2)
IS NOT NULL

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved