Home » SQL & PL/SQL » SQL & PL/SQL » Pls help in writing query
Pls help in writing query [message #331217] Wed, 02 July 2008 11:16 Go to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
I have got a huge table called BUILDING and i am listing the columns related to my query here:-
bld_id number(10);
route_id number(10);
x number;
y number;

A little bit of explanation:-
bld_id =indicates a building id(primary key)
route_id =Route id and each building belongs to a route and there are multiple buildings in a route.
x and y -Co ordinates for a particular building which uniquely identifies a building
Various other columns are there which i am not listing here.

Now my task is,
Some buildings are on an "UNASSIGNED ROUTE"(I get which are these from a route table-There are a number of routes which are "UNASSIGNED")
Step1
I need to find out the buildings within an area first-The buildings is this area
means each building within x-50 and x+50 and y-50 and y+50 of a particular building
(ie, each building is taken as a central point)
Step2
Now find out which are the routes for those buildings(or how may routes)
Step3
If all these routes are same(ie, the surrounding buildings within x-50 and x+50 and y-50 and y+50 are
all in a unique route) then assign that particular route id to the building instead of
"UNASSIGNED ROUTE ID" ...
if it is 2 or more routes then then leave it..that is, do not update the building.
I need to do this taking care of every building in an "UNASSIGNED ROUTE"

What is the most efficient way to write this query?
Pls give me the simplest code to perform this query or atleast ideas--Is this really complex and how long it will take?
Pls help in writing query [message #331218 is a reply to message #331217] Wed, 02 July 2008 11:17 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
I have got a huge table called BUILDING and i am listing the columns related to my query here:-
bld_id number(10);
route_id number(10);
x number;
y number;

A little bit of explanation:-
bld_id =indicates a building id(primary key)
route_id =Route id and each building belongs to a route and there are multiple buildings in a route.
x and y -Co ordinates for a particular building which uniquely identifies a building
Various other columns are there which i am not listing here.

Now my task is,
Some buildings are on an "UNASSIGNED ROUTE"(I get which are these from a route table-There are a number of routes which are "UNASSIGNED")
Step1
I need to find out the buildings within an area first-The buildings is this area
means each building within x-50 and x+50 and y-50 and y+50 of a particular building
(ie, each building is taken as a central point)
Step2
Now find out which are the routes for those buildings(or how may routes)
Step3
If all these routes are same(ie, the surrounding buildings within x-50 and x+50 and y-50 and y+50 are
all in a unique route) then assign that particular route id to the building instead of
"UNASSIGNED ROUTE ID" ...
if it is 2 or more routes then then leave it..that is, do not update the building.
I need to do this taking care of every building in an "UNASSIGNED ROUTE"

What is the most efficient way to write this query?
Pls give me the simplest code to perform this query or atleast ideas??
Re: Pls help in writing query [message #331220 is a reply to message #331218] Wed, 02 July 2008 11:25 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
This problem post seems to be a restatement of the proverbial Traveling Salesman problem.

The actual solution is just a Simple Matter Of Programming (SMOP) &
really, really has little or nothing to do with Oracle itself.
Re: Pls help in writing query [message #331382 is a reply to message #331220] Thu, 03 July 2008 03:54 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
I know that it is related to programming only
Anyhow, I put this post not just for wasting my time, but expecting some help.
I am looking for the code even if it is not complete..just want to know abt the loopings and right structure..
Can someone really help?I would be grateful in that case..
Re: Pls help in writing query [message #331512 is a reply to message #331382] Thu, 03 July 2008 13:10 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Try asking your question again after reading and following ALL of the posting guidelines

[Updated on: Thu, 03 July 2008 13:11]

Report message to a moderator

Previous Topic: select statement help?
Next Topic: Error checking in procedure
Goto Forum:
  


Current Time: Sun Dec 04 04:58:45 CST 2016

Total time taken to generate the page: 0.05870 seconds