Informix SQL help
Given the data in the 'route' table listed below, I need to retrieve the most frequent route taken between locations '7PC' and 'REE'. The following routes are detailed in the table below 7PC to KMCI KMCI to OKL OKL to REE (Refer to route_id's 1 -3) 7PC to KMCI KMCI to OKL OKL to KELP KELP to REE (Refer to route_id's 4 -15) 7PC to KMCI KMCI to OKL OKL to 6PM 6PM to 6J8 6J8 to REE (Refer to route_id's 15 -25) Expected Results - The most frequent route used: 7PC to KMCI KMCI to OKL OKL to KELP KELP to REE Analyzing the given data below, the above route occurred 3 times. The 1st route (refer to route_id's 1-3) occurred only 1 time, and the 3rd route (refer to route id's 15-25) occurred only 2 times. I am totally lost as to how to retrieve the above expected results using SQL. Any help would be greatly appreciated. Thanks Kirk Note: If needed, the table below can be cut and pasted into an Excel spreadsheet. route_id movement_no mvnt_on_trip_id start_loc_code end_location_code 1 1110091 1 7PC KMCI 2 1110091 2 KMCI OKL 3 1110091 3 OKL REE 4 1128647 1 7PC KMCI 5 1128647 2 KMCI OKL 6 1128647 3 OKL KELP 7 1128647 4 KELP REE 8 1128679 1 7PC KMCI 9 1128679 2 KMCI OKL 10 1128679 3 OKL KELP 11 1128679 4 KELP REE 12 1130202 1 7PC KMCI 13 1130202 2 KMCI OKL 14 1130202 3 OKL KELP 15 1130202 4 KELP REE 16 1132208 1 7PC KMCI 17 1132208 2 KMCI OKL 18 1132208 3 OKL 6PM 19 1132208 4 6PM 6J8 20 1132208 5 6J8 REE 21 1150727 1 7PC KMCI 22 1150727 2 KMCI OKL 23 1150727 3 OKL 6PM 24 1150727 4 6PM 6J8 25 1150727 5 6J8 REE
Because these are essentially Hierarchical data there is not a good way
to handle this in a relational database. If you can get the excellent
node datablade by Jacques Roy from the informix site you could use this
to find the best paths. It would be the best longterm solution.
Here is a website with Jacques Roy's presentation at a user conference
that I went to.
http://www.iiug.org/waiug/present/Forum2005/Forum2005Sessions.html
It is on Friday December 9 at 9:30 AM in section C4P.
E7 IDS Extensibility for Business Advantage - Jacques Roy
I would use a stored procedure to build paths. I of course would use a
view at some point. ;-)
Here is my quick solution to the problem (quick meaning it might not be
right but I can't spend anymore time with it. So check it and modify it
as needed):
create table routes(
route_id serial,
movement_no integer,
mvnt_on_trip_id integer,
start_loc_code char(4),
end_loc_code char(4)
);
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 1, 1110091, 1, "7PC", "KMCI");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 2, 1110091, 2, "KMCI", "OKL");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 3, 1110091, 3, "OKL", "REE");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 4, 1128647, 1, "7PC", "KMCI");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 5, 1128647, 2, "KMCI", "OKL");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 6, 1128647, 3, "OKL", "KELP");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 7, 1128647, 4, "KELP", "REE");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 8, 1128679, 1, "7PC", "KMCI");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 9, 1128679, 2, "KMCI", "OKL");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 10, 1128679, 3, "OKL", "KELP");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 11, 1128679, 4, "KELP", "REE");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 12, 1130202, 1, "7PC", "KMCI");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 13, 1130202, 2, "KMCI", "OKL");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 14, 1130202, 3, "OKL", "KELP");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 15, 1130202, 4, "KELP", "REE");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 16, 1132208, 1, "7PC", "KMCI");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 17, 1132208, 2, "KMCI", "OKL");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 18, 1132208, 3, "OKL", "6PM");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 19, 1132208, 4, "6PM", "6J8");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 20, 1132208, 5, "6J8", "REE");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 21, 1150727, 1, "7PC", "KMCI");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 22, 1150727, 2, "KMCI", "OKL");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 23, 1150727, 3, "OKL", "6PM");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 24, 1150727, 4, "6PM", "6J8");
insert into routes(route_id, movement_no, mvnt_on_trip_id,
start_loc_code, end_loc_code)
values( 25, 1150727, 5, "6J8", "REE");
create table trips(
trips serial,
movement_no integer,
path varchar(255) --Might need more
) ;
create function generate_trips( input_move_no integer ) returningvarchar(255) ;
define newPath varchar(255);
define step_id integer;
define start_node char(4);
define stop_node char(4);
let newPath = "";
foreach
select
mvnt_on_trip_id,
start_loc_code,
end_loc_code
into
step_id,
start_node,
stop_node
from
routes
where
movement_no = input_move_no
order by
mvnt_on_trip_id
let newPath = newPath || start_node || " - " ;
end foreach
let newPath = newPath || stop_node ;
return newPath;
end function;
drop view movements;
create view movements(movement_no, start_loc_code, end_loc_code) as
select
sr.movement_no,
sr.start_loc_code,
er.end_loc_code
from
routes sr,
routes er
where
sr.movement_no = er.movement_no and
sr.mvnt_on_trip_id = 1 and
er.mvnt_on_trip_id = (select max(mvnt_on_trip_id) from routes r
where r.movement_no = er.movement_no);
-- For visual check
select * from routes ;
-- For visual check
select * from movements;
--Builds trip table
insert into trips select 0, movement_no, generate_trips(movement_no)
from movements ;
> -----Original Message----- > From: Curtis Crowson [mailto:curtis.crowson@employease.com] > Posted At: Monday, February 06, 2006 9:18 AM > Posted To: comp.databases.informix > Conversation: Informix SQL help > Subject: Re: Informix SQL help > > > Because these are essentially Hierarchical data there is not > a good way > to handle this in a relational database. If you can get the excellent > node datablade by Jacques Roy from the informix site you > could use this [cutting] Think this was written by Paul Brown, if you want a depth of more than 16 on the node tree you need to 'tweak' the code, if you can't suss out where drop me a line and I'll look it up Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 GO FURTHER with DB2 GET THERE FASTER with Informix. Attend the IDUG 2006 North America Conference. Tampa, Florida, USA. 7-11 May 2006. Visit http://www.iiug.org/conf for more information.
Paul Watson wrote: > Think this was written by Paul Brown, if you want a depth of more than > 16 on the node tree you need to 'tweak' the code, if you can't suss out > where drop me a line and I'll look it up Oh, I just thought it was Jacques Roy because he did the presentation. I don't really know. I really hate misattributing work. I hope I didn't offend anyone.
> Paul Watson wrote: > > > Think this was written by Paul Brown, if you want a depth > of more than > > 16 on the node tree you need to 'tweak' the code, if you > can't suss out > > where drop me a line and I'll look it up > > Oh, I just thought it was Jacques Roy because he did the presentation. > I don't really know. I really hate misattributing work. I > hope I didn't > offend anyone. > I doubt it - most the regulars on CDI are offensive enough Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 GO FURTHER with DB2 GET THERE FASTER with Informix. Attend the IDUG 2006 North America Conference. Tampa, Florida, USA. 7-11 May 2006. Visit http://www.iiug.org/conf for more information.