SQL challenge

Posted on 1999-01-04
Last Modified: 2011-09-20
I'm trying to retrieve data about a flight leg.
ie. A flight from Japan to New York can be a direct flight
or can have transition. So it will become pt A to pt B then
to pt. C.

Below is my sample code. Is there any problem ?
a.carrier_code, a.flight_num, a.period_from, a.period_to, a.DOP,
a.dept_station, a.dept_passenger_STD, b.arrv_station, a.arrv_passenger_STA,
FROM sia_segment a, sia_segment b
WHERE (b.dept_station = a.arrv_station AND
b.arrv_station = '$arrv_station' AND
a.dept_station = '$dept_station' AND
a.period_from <= '$flight_date' AND
a.period_to >= '$flight_date')
Question by:slok
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2

Expert Comment

ID: 1026188
Which info are you trying to get? The whole flight schedule, or just a part of it? How many legs are possible? Is it possible to have a flight go A to B to C to D?


Author Comment

ID: 1026189
I'm trying to get all the flight info.
It is possible to have up A->B->C->D.


Expert Comment

ID: 1026190
would where b.arrv_station = '$arrv_station' AND
a.dept_station = '$dept_station' AND b.flight_num=a.flight_Num
and <the rest of your query>

work for you?  You should have the same flight_num in all matching segments shouldn't you?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 1026191
What database are you using? Just wondering, because I don't think you can use the "$" character in your SQL statement under MS Access. The ? character matches any single alphabetic character, for instance B?ll finds ball, bell, and bill. The #      character matches any single numeric character.      For instance 1#3 finds 103, 113, 123. I think you'll need to use the ? character. If you're not using Access, you may double check which wildcards you can use in you SQL statement.

I hope this helps...

Expert Comment

ID: 1026192
What you're asking for is not simple, for 2 reasons:

1. you want to "walk the tree" for as many legs as it takes to get to the destination
2. you only want to display trees (itineraries) whose last node is your final destination

It is far simpler and more efficient to do this within a procedure. However, if you insist on doing this within a single query, you'll want to use your database's equivalent of the "Connect by" clause. Below is an example in Oracle. If this is the type of thing you want, let me know and I'll try to explain it.

/* My table of flights. It has lots of different flights between cities A through F. You want to get
  to get from A to D. */
SQL> select * from yoren_tmp;
------------ ------------
A            D
A            B
C            D
B            C
A            E
D            F
A            C
C            E

/* The query below shows all the possible ways to get from A to D */
SQL> get tmp
  1  select itinerary_id, dept_station,arrv_station
  2  from
  3  (select rownum-level itinerary_id,
  4   dept_station,arrv_station
  5   from yoren_tmp
  6   where dept_station != '&&to'
  7   start with dept_station = '&&from'
  8   connect by prior arrv_station = dept_station
  9  ) a
 10  where exists
 11   (select * from
 12     (select rownum-level itinerary_id,
 13      dept_station,arrv_station
 14      from yoren_tmp
 15      where dept_station != '&&to'
 16      start with dept_station = '&&from'
 17      connect by prior arrv_station = dept_station
 18     ) b
 19    where a.itinerary_id = b.itinerary_id and
 20*   b.arrv_station = '&&to')
SQL> /
Enter value for to: D
old   6:  where dept_station != '&&to'
new   6:  where dept_station != 'D'
Enter value for from: A
old   7:  start with dept_station = '&&from'
new   7:  start with dept_station = 'A'
old  15:     where dept_station != '&&to'
new  15:     where dept_station != 'D'
old  16:     start with dept_station = '&&from'
new  16:     start with dept_station = 'A'
old  20:   b.arrv_station = '&&to')
new  20:   b.arrv_station = 'D')
------------ ------------ ------------
           0 A            D
           1 A            B
             B            C
             C            D
           6 A            C
             C            D
6 rows selected.


Accepted Solution

yoren earned 100 total points
ID: 1026193
See my comment and accept if it answered your question.

Author Comment

ID: 1026194
Yoren : I would need some explaination on the code. FYI : I'm using Informix database
with their Web datablade.The web datablade is the tool that I use to build a front end query form.


Expert Comment

ID: 1026195
Yes Yoren, as slok has pointed out, we're using INFORMIX Universal Server here. I have looked through the manual and I can't find anything on "connect by" or even recursive query. I believe that's a special SQL extension to Oracle. Would we be able to do all that fun stuff with standard ANSI(92?) SQL?


Author Comment

ID: 1026196
Adjusted points to 100

Expert Comment

ID: 1026197
I got confirmation from the Informix community that it doesn't have an equivalent command to Oracle's CONNECT BY. We have to code the logic by hand...


Expert Comment

ID: 1026198
leehaw and slok,

Basically, Oracle's connect by is just a way of doing a recursive loop in a query. The best way to solve your problem is really in a procedure. The logic would be to take each flight and follow it to each possible end and take the ones that end at your destination. Since this may lead to many, many possibilities, you might want to limit the iterating to a maximum number of segments; 4, for example. So, if your procedure doesn't reach the destination within 4 legs, it moves on.

If you will have lots of hits to this database, you may want to consider creating pre-computed tables of departures and destinations. Something like this:

Table "Flight_Segments" is your main table: (Flight_ID,From,To,Departure_Time,Arrival_Time)

Table "Itineraries" columns: (Itinerary_ID,From,To,Departure_Time,Arrival_Time)
Table "Flight_Paths" columns: (Itinerary_ID,Sequence,Flight_ID)

Flight_Segments has info on each flight.
Itineraries has the From location and eventual destination.
Flight_Paths has the list of segments you take to complete the itinerary.

Hope this helps,



Expert Comment

ID: 1026199
Thanks for your reply. Opens up a whole new SQL world for me. :)

Just a note, the "Parts Explosion" problem is slightly different to my "Connecting Flight" problem. For parts explosion, it's purely parent-child relationship. But for connecting flights, it's more complex since we have flights to and fro.

So, if the table contains this:

Depart     Arrive
------     ------
A          B
B          C
A          C
C          A
C          B
B          A

Then, using the parts explosion logic, when query for a connecting flight that starts with "A" and ends with "C", we might get:

A  C
A  B  C
A  B  A  C
A  C  A  C
A  B  C  A  C

Maybe a more complex SQL statement needed? Or am I imagining things?

Thanks, tata.

In article <77l3fl$>,
  "Dennis Pimple" <> wrote:
> As far as I can tell, Informix SQL doesn't have a direct equivalent to START
> WITH or CONNECT BY PRIOR. Your specific problem can be solved directly in a
> single SQL, with the caveat that we have to set a maximum number of hops.
> Here's my test for up to four hops (cribbed from Informix Guide to SQL
> Version 7.2 Tutorial page 5-29):

> create table route( start char(1), end char(1));

> insert into route values ("A", "B");
> insert into route values ("A", "C");
> insert into route values ("A", "D");
> insert into route values ("A", "E");
> insert into route values ("A", "G");
> insert into route values ("G", "H");
> insert into route values ("H", "I");
> insert into route values ("H", "E");
> insert into route values ("I", "E");
> insert into route values ("G", "B");
> insert into route values ("D", "F");
> insert into route values ("B", "C");
> insert into route values ("B", "E");
> insert into route values ("C", "E");

> select one.start, one.end, two.end, three.end, last.end
> from route one,
>      outer (route two,
>             outer (route three, outer route last))
> where one.start = "A"
> and last.end = "E"
> and one.end = two.start
> and two.end = three.start
> and three.end = last.start
> order by last.end, three.end, two.end, one.end

> Output:
> A     E
> A     B   E
> A     C   E
> A     D   F
> A     G   B   E
> A     B   C   E
> A     G   H   E
> A     G   B   C   E
> A     G   H   I   E

> Since Informix SQL doesn't have a way to reference a prior row, I don't
> think that a no-upper-limit solution is achievable. As your Oracle
> respondent points out, in 4GL, SPL, or other procedural language, this is a
> variation of the parts-explosion problem and readily (but not trivially)
> resolved. If you want to see it in 4GL, say "Please" and I'll write-and-post
> it.


Author Comment

ID: 1026200
thanks a lot

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question