SELECT and NOT IN a dynamic sequence
-
Hi all, I need to be able to do a query for those values that are not in a particular field in a recordset. So lets say I have a table with a field (indexnum) which is a text string of a hex number. eg "00034F12". That number is incremented for each record that is added. Legacy code, so I don't know why it is done this way. What i need to do is query for all indexnum's that don't occur given a list of indexnums. Indexnums will be between 00000001 and 0x000FFFFF. These are not hardcoded, so at the time this code will be run, it maybe between 0034829F and 00349000. So some how I need something like : SELECT indexnum from tblData NOT IN ( SELECT ... [build up a temp table or something or a sequence of number from the starting and ending nummbers I give it] ) Can this be done somehow? I need it to work on DB's like Oracle and MS Access, and I don't think Access supports stored procedure (although I don't know much about these). Thanx If there's one thing I've learned, it's that life is one crushing defeat after another until you just wish Flanders was dead. - Homer Simpson
-
Hi all, I need to be able to do a query for those values that are not in a particular field in a recordset. So lets say I have a table with a field (indexnum) which is a text string of a hex number. eg "00034F12". That number is incremented for each record that is added. Legacy code, so I don't know why it is done this way. What i need to do is query for all indexnum's that don't occur given a list of indexnums. Indexnums will be between 00000001 and 0x000FFFFF. These are not hardcoded, so at the time this code will be run, it maybe between 0034829F and 00349000. So some how I need something like : SELECT indexnum from tblData NOT IN ( SELECT ... [build up a temp table or something or a sequence of number from the starting and ending nummbers I give it] ) Can this be done somehow? I need it to work on DB's like Oracle and MS Access, and I don't think Access supports stored procedure (although I don't know much about these). Thanx If there's one thing I've learned, it's that life is one crushing defeat after another until you just wish Flanders was dead. - Homer Simpson
What about a simple WHERE clause on indexnum?
select indexnum
from tblData
where not (indexnum between @lowerBound and @higherBound)or maybe
select indexnum
from tblData
where not (indexnum >= @lowerBound and indexnum <= @higherBound) -
What about a simple WHERE clause on indexnum?
select indexnum
from tblData
where not (indexnum between @lowerBound and @higherBound)or maybe
select indexnum
from tblData
where not (indexnum >= @lowerBound and indexnum <= @higherBound)This is me being lazy and not trying it, but asking a question first :) Given that indexnum is a text string, will this comparison work? For example : "00000007" "00000008" "0000000C" if we ran the sql query for "00000007" to "0000000C", would it return : "00000009" "0000000A" "0000000B" ???? I didn't think the database would be so smart as to count in hex using text strings. If there's one thing I've learned, it's that life is one crushing defeat after another until you just wish Flanders was dead. - Homer Simpson
-
This is me being lazy and not trying it, but asking a question first :) Given that indexnum is a text string, will this comparison work? For example : "00000007" "00000008" "0000000C" if we ran the sql query for "00000007" to "0000000C", would it return : "00000009" "0000000A" "0000000B" ???? I didn't think the database would be so smart as to count in hex using text strings. If there's one thing I've learned, it's that life is one crushing defeat after another until you just wish Flanders was dead. - Homer Simpson
Well, if your being lazy means more work for me, then I one-up your laziness with some of my own :) Try it out and see what you get. I think most databases consider "8,9,A,B,C" an ascending text sort.
-
Well, if your being lazy means more work for me, then I one-up your laziness with some of my own :) Try it out and see what you get. I think most databases consider "8,9,A,B,C" an ascending text sort.
Well touche. I respect your lazyness and bow down to it. I've been out-slothed. I will therefore try this out :) hehe Thanx Mike. If there's one thing I've learned, it's that life is one crushing defeat after another until you just wish Flanders was dead. - Homer Simpson