Postal Code / Zip Code Numerical Range Search

Classic Servoy
1

I’m trying to perfect a numerical range search via two zip codes, smallest to largest…

So I have two integer globals attached to type ahead fields so the user can easily choose the codes…

//zip code between ? and ?
if(globals.searchFieldInt01 || globals.searchFieldInt02) {
	bAddress = true;
    input = input.concat([Number(globals.searchFieldInt01), Number(globals.searchFieldInt02)]);
    //input = input.concat([globals.searchFieldInt01, globals.searchFieldInt02]);

    //query += "zipcode BETWEEN ? AND ? AND ";
    //query += "CONVERT( number(10), " + zipcode + ") BETWEEN ? AND ? AND ";
    query += "address.postal_code BETWEEN ? AND ? AND ";
    //query += "CAST(address.postal_code AS FLOAT) = address.postal_code AND ";
    //query += "CONVERT( number(10),address.postal_code) BETWEEN ? AND ? AND ";
}

I left in the commented out code so you can see the evolution of this process running on Sybase 9.01.

Currently here is the final build of the sql statement:

sQuery: SELECT DISTINCT contact.contact_id FROM contact , address WHERE contact.contact_id=address.contact_id AND address.postal_code BETWEEN ? AND ? ORDER BY contact.contact_id;

And here is the error I’m getting:

com.servoy.j2db.dataprocessing.DataException: ASA Error -157: Cannot convert L0S 1J0 to a numeric(30,6)

I hope this is enough information to spur any new ideas.

Thanks in advance for all your help

2

What kind of valuelist do you have on your typeahead fields?

3

It’s a type ahead field with a value list that returns the postal codes in the address table.

4

What happens if you try this:

input = input.concat([globals.searchFieldInt01 * 1, globals.searchFieldInt02 * 1]);
5

Same error:

com.servoy.j2db.dataprocessing.DataException: ASA Error -131: Syntax error near 'Number' on line 1

I’m also using the last line in the code attempts:

query += "CONVERT( Number(10),address.postal_code) BETWEEN ? AND ? AND ";
6

This works for US Zip Codes that are purely 5 - 7 digits and non-alpha… lexiconically…

//zip code between ? and ?
if(globals.searchFieldInt01 || globals.searchFieldInt02) {
	bAddress = true;
    input = input.concat([ String(utils.stringToNumber(globals.searchFieldInt01)), String(utils.stringToNumber(globals.searchFieldInt02))]);
    query += "address.postal_code BETWEEN ? AND ? AND ";
}
7

Ok, now I understand what you’re trying to do. :)

Using “convert” would work if all your postal_codes could be converted to a number, but because you also have values like “L0S 1J0”, it doesn’t work.

I don’t see a real solution for this, you should either get rid of the not-convertible values, not use a numerical range, or solve it in a more circuitous way like using a stored calc that gets the int value of the postal_code.

Maybe someone else has a better solution…