SQL challenge

Posted on 1999-01-04
Medium Priority
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?
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.


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 400 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$ra61@webint.na.informix.com>,
  "Dennis Pimple" <dennisp@informix.com> 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

718 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