We help IT Professionals succeed at work.

Query to find the range of postal codes in Oracle

sandy240887
sandy240887 asked
on
551 Views
Last Modified: 2012-06-22
Hi All,
I need a query where I need the range of postal codes for a
particular county.The query which I wrote gives me
cust_account_number,county_name,state(county_name and state from a custom table and cust_account_number from the hz_cust_accounts table).Now I want two
more columns from_postal_code and to_postal_code.Is there any way in
which I can find out the range of the postal codes for a particular
county from any standard receivables table.

Any help would be really appreciated
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
do you already have the postal codes somewhere?  if so, what is the structure of that table and how can it be joined to what you already have?
A bit more detail on the tables involved would be useful... what columns are there, sample data, what do you expect as output?

In general, you can use the MIN and MAX functions to find out the minimum and maximum values in a column. E.g. with a table containing country and zip_code as columns, you could retrieve the zip code ranges as follows:

SELECT
  country
  ,MIN(zip_code)
  ,MAX(zip_code)
FROM
  <table>
GROUP BY
  country

Author

Commented:
There is a custom table xx_counties which has columns  cust_account_id,county_name,state,start_date,end_date   (only selected customers).
I need a query which can fetch the above columns and also two other columns which specify  the range of postal codes( from_postal_code and to_postal_code) for the respective fields.
 
This custom table does not have postal codes column.So Now I want to know if there is any way where in I can find out the range of the postal codes for a particular
county from any standard receivables table.
Can you describe your receivables table?
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
why the B?  If you needed more info, please ask rather before assigning penalties.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.