Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, jahangir.

Asked: November 26, 2015 - 1:51 pm UTC

Last updated: December 08, 2015 - 6:26 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

AIRPLANE TABLE
airlane_id type Total_seats Starting rang A Ending range A Starting rang B Ending range B Starting rang C Ending range c
1 Airbus 350 1 30 31 100 101 350

2 Jambojet 400 1 50 51 100 101 450



FLIGHT_SHEDULE

FLIGHT ID DEPARTURE DATE SOURCE DESTINATION ARIVAL DATE KEY
PK123 20-11-15 PEW JEDAH 21-11-15 GENERATE
BR140 25-11-15 KARACHI DUBAI 26-11-15 GENERATE

WHEN CLICK ON GENERATE SO I WANT THE BELOW TABLE TO GENERATE AND LOOKING COULUMNS FROM TABLE AIRPLANE TABLE DEPENDING UPON THE COLUMNS TOTAL SEATS, SARTING RANGE A, ENDING RANGE A, SARTING RANGE B, ENDING RANGE B ,STARTING RANGE C, ENDING RANGE C

FLIGHT_SEATING_DEATAIL
FLIGHT ID _FK DEPARTURE DATE_FK SEAT NO CLASS
PK123 20-11-15 1 A
PK123 20-11-15 2 A
.
.REPEATING repeating pk key
REPEATING




and Chris said...

Please, DON'T SHOUT!

You need to generate rows from up to the maximum number of seats. If you cross join this with the relevant aircraft, you can use a case expression to determine which class each seat is in.

Here's something to get you started:

create table airplanes (
  airplane_id integer,
  start_a integer,
  end_a integer,
  start_b integer,
  end_b integer,
  start_c integer,
  end_c integer
);

create table flight_seats (
  flight_id integer,
  dep_date  date,
  seat_no   integer,
  class     varchar2(1)
);

insert into airplanes values (1, 1, 30, 31, 100, 101, 350);

insert into flight_seats
with seats as (
  select rownum r 
  from   dual
  connect by level <= 350
)
  select 1 flt_id, 
         date'2015-01-01' dep_date, 
         r seat,
         case 
           when r between start_a and end_a then 'A'
           when r between start_b and end_b then 'B'
           when r between start_c and end_c then 'C'
         end class
  from   seats, airplanes;

350 rows inserted.

select * from flight_seats
order  by seat_no;

 FLIGHT_ID DEP_DATE       SEAT_NO C
---------- ----------- ---------- -
         1 01 Jan 2015          1 A
         1 01 Jan 2015          2 A
... <snip> ...      
         1 01 Jan 2015         30 A
         1 01 Jan 2015         31 B
... <snip> ...      
         1 01 Jan 2015        100 B
         1 01 Jan 2015        101 C
... <snip> ...      
         1 01 Jan 2015        350 C

 350 rows selected

You'll need to update this to select the relevant aircraft type and limit the total rows appropriately.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

good

jahangir khan, November 28, 2015 - 10:24 pm UTC

good answer sir thanks

jahangir khan, December 06, 2015 - 9:37 am UTC

but i want to connect it with airplane as well with flight_shedule table i want such as bleow:

select flight_id.flight_sheduel,
dep_date.flight_sheduel,
r seat,
case
when r between start_a and end_a then 'A'
when r between start_b and end_b then 'B'
when r between start_c and end_c then 'C'
end class
from seats, airplanes,flight_shedule;

note:want to populate seat_table with data that exists in airplane_table and flight_sheduel i tried but it shows error can u solve it sir.

Connor McDonald
December 06, 2015 - 5:52 pm UTC

I'm not clear what the issue is. What error do you get?

Note - R is just a made up column for the example I did. I'm guessing you want to replace this with the seat number column from the seats table.

good

jahangir khan, December 07, 2015 - 9:59 am UTC

Note: IN below table i want DEP_DATE , and flight_id from flight_sheduel table, and seat_ranges from seat_table mean to retrieve and then populate the seat_table dynamically not manually ? the out put is correct but need logic to made insertion in seats_table dynamically for each airplane ? To write code a same code for each button that look up from both tables and made insertion in seats_table ?

FLIGHT_ID DEP_DATE SEAT_NO C
---------- ----------- ---------- -
1 01 Jan 2015 1 A //flight_id and dep_date take dynamically
1 01 Jan 2015 2 A
... <snip> ...
1 01 Jan 2015 30 A
1 01 Jan 2015 31 B
... <snip> ...
1 01 Jan 2015 100 B
1 01 Jan 2015 101 C
... <snip> ...
1 01 Jan 2015 350 C

So add that in.

Paul, December 07, 2015 - 8:50 pm UTC

What Chris shows you is how to generate rows (made up data) to join to your model.

What you would need in your flight_schedule table is a key to tell you what type of aircraft was flying that route that day. a Relationship between flightcode and an aircraft.

Then join the 3 tables.
Note: this query here creates 350 rows as a maximum number of rows whether it needs to or not. If a plane only has 290 seats (like my sample below) you just add a where clause to limit the return rows.

with 
seats as (
  select rownum r 
  from   dual
  connect by level <= 350
)
  select flight_schedule.id,
         flight_schedule.dep_date, 
         r seat,
         case 
           when r between start_a and end_a then 'A'
           when r between start_b and end_b then 'B'
           when r between start_c and end_c then 'C'
         end class
  from   seats, airplanes, flight_schedule
  where flight_schedule.airplane_id=airplanes.airplane_id
  and r <= airplanes.end_c;
  

ID DEP_DATE SEAT CLASS
101 24-DEC-15 290 C
101 24-DEC-15 289 C
[..]
101 24-DEC-15 101 C
101 24-DEC-15 100 B
[..]
101 24-DEC-15 7 A
101 24-DEC-15 6 A
101 24-DEC-15 5 A
101 24-DEC-15 4 A
101 24-DEC-15 3 A
101 24-DEC-15 2 A
101 24-DEC-15 1 A


And do with those rows what you will.

Chris Saxon
December 08, 2015 - 6:26 am UTC

Thanks for the contribution.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.