asked on

Oracle query - Find postal code between a from - to range

Hi,

I have this below query that i would like to adjust so that i can find the ZONE_ID from a postal code that will fall inside a postal code from and to range.

For example:

If i have this below zones and postal code range in column 7 and 8 and i would like to see what what would be the postal code ZONE_ID for postal: "V5A0B1" i'm expecting to see ZONE_3.

Table looks like:

Here is the query so far:

SELECT 
   ZONE_ID, 
   ZONE_DESCRIPTION, 
   NULL, 
   COUNTRY, 
   CITY, 
   STATE, 
   POSTAL_CODE_FROM, 
   POSTAL_CODE_TO, 
   NULL
FROM 
  ZONE_TABLE
WHERE 
   (POSTAL_CODE_FROM => 'V5A0B1'
   AND POSTAL_CODE_TO =< 'V5A0B1');

Open in new window

Thanks for your help

awking00

What is wrong with the query you have so far? It seems to be making an ascii comparison okay.

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

It does not give me any results.

SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

Thats probably why and 'V5A0B1' between GAT.USR_PSTL_CD_FROM and GAT.USR_PSTL_CD_TO gives me a record

Rgonzo1971

then try

(POSTAL_CODE_FROM <= 'V5A0B1'
AND POSTAL_CODE_TO >= 'V5A0B1');

awking00

Rgonzo1971's remark is precisely why you're not getting any records, I'm so accustomed to writing comparison operators, I automatically did so when I tested the comparisons plus the between ... and method doesn't need those operators.

Wilder1626

ASKER

Thanks to both of you, it work great.

I also like the between one. First time i use it.

awking00

Just a note on using between. That operator is inclusive meaning, for example, between 0 and 10 will match 0 and 10 and anything in between. This gets a little trickier when using dates. Where date between 01-JUN-16 and 30-JUN-16,
would not include any records from 30-JUN-16 unless they were exactly midnight. When making date comparisons like this it is better to use where date >= 01-JUN-16 and date < 01-JUL-16.

Wilder1626

ASKER

Thanks for the information.