Search on field

[tjsingh]

Hi

I am using character based progress at my company.

ok i have a table for stock where the location is set to e.g c20, c110 etc

but when i run a query on the table if i check location >= "c30" and <= "c40"
it shows the records but it also includes "c4" which i dont want

any ideas? it just finds all number patterns such as c3 and c30 etc....

for each stock where klocsn >= "c30" and
klocsn <= "c40" and
kco = 1 and
stock.act-qty[1] > 0 no-lock:
display stock.klocsn.
end.

 

[mwm]

It depends on what you want to do.

If you want a minimum of 3-character locations, then include this condition: "and LENGTH(klocsn) > 2".

 

[tjsingh]

Hi

Thanks for reply

Well that wont work as if i am doing a search say for locations between c30 and c40 it will still return location c4 along with the others between c30 and c40. It must take accoutn of all character combinations such as c4 and c40 and do the search based on that.

So if i did a search with length(klocsn) > 2 and entered c1-c4 it would not return any results as the locations range from c1-c120

cant seem to find a way to get around this at the mo

cheers

 

[mwm]

So you are trying to skip locations which have only 2 characters?

Did you try adding my suggested condition to your query? I believe it will work. In your 2nd example (a query for the range c1-c4), locations of c15 & c1a would fall in the range with your query, but if you added my "LENGTH" condition, they would not.

I think you're getting confused about ranges & comparisons. When you run the query for a range of c1-c4, it's not going to return only 2-character locations. If you had a location of c1234567890 (which I know you don't but just am making an obvious example), it would be included in that query. In other words, it's an alphanumeric comparison for the entire string, not an alphanumeric comparison just for the first character, and then a numeric comparison for everything else.

Try this: "display 'c15' > 'c2'". You will get "NO", but I think you're expecting "YES".

 

[tamhas]

I think you have two choices ... either include a length test in your comparison, e.g., = 3 for only selecting c30 to c40, or you are going to have to parse the location into alphabetic and numeric pieces and do the comparison on each piece separately. Either way, with that ID scheme, you are going to have to read a lot of records that don't qualify, so be careful how you do it. E.g., if there are lot of records, you might want to read no-lock and then reread a buffer exclusive-lock so that you aren't locking a whole bunch of unwanted records.