tables:
airplane(airplane_id,type,total_seats,start_a,end_a,start_b,end_b,start_c,end_c)
flight_shedule(flight_id,departure_date,source,destination,arrival_date)
flight_seats(flight_id,departure_date,seat_no,class,fare,status)
I have created a interactive reports on these tables. in flight_shedule report i made a html botton and plsql process i join these two. its working when i press button but only when i have one entry in flight_shedule tables.but it fails when there are many entries in flight_shedule table.i-e where clause meats in more than two rows...
plsql code:
begin
insert into FLIGHT_SEATS
with
seats as (
select rownum r
from dual
connect by level <= 350
)
select flight_shedule.flight_id,
flight_shedule.departure_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,900 fare,'o' status
from seats, airplane, flight_shedule
<b>where flight_shedule.airplane_id=airplane.airplane_id </b>;
this code will works when only one where clause matches.how i update my code so that it works when more than where clause matches.
and works properly and insert the data successfully....help me in this regard.
Not entirely sure I understand the requirements, but I made some slight amendments which I think will help.
SQL> drop table airplane purge;
Table dropped.
SQL> drop table flight_shedule purge;
Table dropped.
SQL> drop table flight_seats purge;
Table dropped.
SQL>
SQL> create table airplane(airplane_id int,plane_type int,total_seats int,start_a int,end_a int ,start_b int ,end_b int ,start_c int ,end_c
int);
Table created.
SQL> create table flight_shedule(airplane_id int, flight_id int,departure_date date,source varchar2(10),destination varchar2(10),arrival_dat
e date);
Table created.
SQL>
SQL> create table flight_seats(flight_id int,departure_date date,seat_no int,class varchar2(1),fare int,status varchar2(1));
Table created.
SQL>
SQL> insert into airplane values (1,1,100,1,10,11,20,21,100);
1 row created.
SQL> insert into flight_shedule values (1,1,trunc(sysdate),'s','d',trunc(sysdate));
1 row created.
SQL>
SQL>
SQL> --insert into FLIGHT_SEATS
SQL> with
2 seats as (
3 select rownum r
4 from dual
5 connect by level <= 350
6 )
7 select flight_shedule.flight_id,
8 flight_shedule.departure_date,
9 r seat,
10 case
11 when r between start_a and end_a then 'A'
12 when r between start_b and end_b then 'B'
13 when r between start_c and end_c then 'C'
14 end class,900 fare,'o' status
15 from seats,
16 airplane,
17 flight_shedule
18 where flight_shedule.airplane_id=airplane.airplane_id
19 and r <= end_c
20 order by 1,2,3;
FLIGHT_ID DEPARTURE SEAT C FARE S
---------- --------- ---------- - ---------- -
1 02-JAN-16 1 A 900 o
1 02-JAN-16 2 A 900 o
1 02-JAN-16 3 A 900 o
1 02-JAN-16 4 A 900 o
1 02-JAN-16 5 A 900 o
1 02-JAN-16 6 A 900 o
1 02-JAN-16 7 A 900 o
1 02-JAN-16 8 A 900 o
1 02-JAN-16 9 A 900 o
1 02-JAN-16 10 A 900 o
1 02-JAN-16 11 B 900 o
1 02-JAN-16 12 B 900 o
1 02-JAN-16 13 B 900 o
1 02-JAN-16 14 B 900 o
1 02-JAN-16 15 B 900 o
1 02-JAN-16 16 B 900 o
1 02-JAN-16 17 B 900 o
1 02-JAN-16 18 B 900 o
1 02-JAN-16 19 B 900 o
1 02-JAN-16 20 B 900 o
1 02-JAN-16 21 C 900 o
1 02-JAN-16 22 C 900 o
1 02-JAN-16 23 C 900 o
1 02-JAN-16 24 C 900 o
1 02-JAN-16 25 C 900 o
1 02-JAN-16 26 C 900 o
1 02-JAN-16 27 C 900 o
1 02-JAN-16 28 C 900 o
1 02-JAN-16 29 C 900 o
1 02-JAN-16 30 C 900 o
1 02-JAN-16 31 C 900 o
1 02-JAN-16 32 C 900 o
1 02-JAN-16 33 C 900 o
1 02-JAN-16 34 C 900 o
1 02-JAN-16 35 C 900 o
1 02-JAN-16 36 C 900 o
1 02-JAN-16 37 C 900 o
1 02-JAN-16 38 C 900 o
1 02-JAN-16 39 C 900 o
1 02-JAN-16 40 C 900 o
1 02-JAN-16 41 C 900 o
1 02-JAN-16 42 C 900 o
1 02-JAN-16 43 C 900 o
1 02-JAN-16 44 C 900 o
1 02-JAN-16 45 C 900 o
1 02-JAN-16 46 C 900 o
1 02-JAN-16 47 C 900 o
1 02-JAN-16 48 C 900 o
1 02-JAN-16 49 C 900 o
1 02-JAN-16 50 C 900 o
1 02-JAN-16 51 C 900 o
1 02-JAN-16 52 C 900 o
1 02-JAN-16 53 C 900 o
1 02-JAN-16 54 C 900 o
1 02-JAN-16 55 C 900 o
1 02-JAN-16 56 C 900 o
1 02-JAN-16 57 C 900 o
1 02-JAN-16 58 C 900 o
1 02-JAN-16 59 C 900 o
1 02-JAN-16 60 C 900 o
1 02-JAN-16 61 C 900 o
1 02-JAN-16 62 C 900 o
1 02-JAN-16 63 C 900 o
1 02-JAN-16 64 C 900 o
1 02-JAN-16 65 C 900 o
1 02-JAN-16 66 C 900 o
1 02-JAN-16 67 C 900 o
1 02-JAN-16 68 C 900 o
1 02-JAN-16 69 C 900 o
1 02-JAN-16 70 C 900 o
1 02-JAN-16 71 C 900 o
1 02-JAN-16 72 C 900 o
1 02-JAN-16 73 C 900 o
1 02-JAN-16 74 C 900 o
1 02-JAN-16 75 C 900 o
1 02-JAN-16 76 C 900 o
1 02-JAN-16 77 C 900 o
1 02-JAN-16 78 C 900 o
1 02-JAN-16 79 C 900 o
1 02-JAN-16 80 C 900 o
1 02-JAN-16 81 C 900 o
1 02-JAN-16 82 C 900 o
1 02-JAN-16 83 C 900 o
1 02-JAN-16 84 C 900 o
1 02-JAN-16 85 C 900 o
1 02-JAN-16 86 C 900 o
1 02-JAN-16 87 C 900 o
1 02-JAN-16 88 C 900 o
1 02-JAN-16 89 C 900 o
1 02-JAN-16 90 C 900 o
1 02-JAN-16 91 C 900 o
1 02-JAN-16 92 C 900 o
1 02-JAN-16 93 C 900 o
1 02-JAN-16 94 C 900 o
1 02-JAN-16 95 C 900 o
1 02-JAN-16 96 C 900 o
1 02-JAN-16 97 C 900 o
1 02-JAN-16 98 C 900 o
1 02-JAN-16 99 C 900 o
1 02-JAN-16 100 C 900 o
100 rows selected.
So that looks OK to me ? ie, you populated 100 seats in total across classes a,b,c.
Then I add another flight (completely new plane)
SQL>
SQL> insert into airplane values (2,2,50,1,15,16,30,31,50);
1 row created.
SQL> insert into flight_shedule values (2,2,trunc(sysdate),'s','d',trunc(sysdate));
1 row created.
SQL>
SQL>
SQL> with
2 seats as (
3 select rownum r
4 from dual
5 connect by level <= 350
6 )
7 select flight_shedule.flight_id,
8 flight_shedule.departure_date,
9 r seat,
10 case
11 when r between start_a and end_a then 'A'
12 when r between start_b and end_b then 'B'
13 when r between start_c and end_c then 'C'
14 end class,900 fare,'o' status
15 from seats,
16 airplane,
17 flight_shedule
18 where flight_shedule.airplane_id=airplane.airplane_id
19 and r <= end_c
20 order by 1,2,3;
FLIGHT_ID DEPARTURE SEAT C FARE S
---------- --------- ---------- - ---------- -
1 02-JAN-16 1 A 900 o
1 02-JAN-16 2 A 900 o
1 02-JAN-16 3 A 900 o
1 02-JAN-16 4 A 900 o
1 02-JAN-16 5 A 900 o
1 02-JAN-16 6 A 900 o
1 02-JAN-16 7 A 900 o
1 02-JAN-16 8 A 900 o
1 02-JAN-16 9 A 900 o
1 02-JAN-16 10 A 900 o
1 02-JAN-16 11 B 900 o
1 02-JAN-16 12 B 900 o
1 02-JAN-16 13 B 900 o
1 02-JAN-16 14 B 900 o
1 02-JAN-16 15 B 900 o
1 02-JAN-16 16 B 900 o
1 02-JAN-16 17 B 900 o
1 02-JAN-16 18 B 900 o
1 02-JAN-16 19 B 900 o
1 02-JAN-16 20 B 900 o
1 02-JAN-16 21 C 900 o
1 02-JAN-16 22 C 900 o
1 02-JAN-16 23 C 900 o
1 02-JAN-16 24 C 900 o
1 02-JAN-16 25 C 900 o
1 02-JAN-16 26 C 900 o
1 02-JAN-16 27 C 900 o
1 02-JAN-16 28 C 900 o
1 02-JAN-16 29 C 900 o
1 02-JAN-16 30 C 900 o
1 02-JAN-16 31 C 900 o
1 02-JAN-16 32 C 900 o
1 02-JAN-16 33 C 900 o
1 02-JAN-16 34 C 900 o
1 02-JAN-16 35 C 900 o
1 02-JAN-16 36 C 900 o
1 02-JAN-16 37 C 900 o
1 02-JAN-16 38 C 900 o
1 02-JAN-16 39 C 900 o
1 02-JAN-16 40 C 900 o
1 02-JAN-16 41 C 900 o
1 02-JAN-16 42 C 900 o
1 02-JAN-16 43 C 900 o
1 02-JAN-16 44 C 900 o
1 02-JAN-16 45 C 900 o
1 02-JAN-16 46 C 900 o
1 02-JAN-16 47 C 900 o
1 02-JAN-16 48 C 900 o
1 02-JAN-16 49 C 900 o
1 02-JAN-16 50 C 900 o
1 02-JAN-16 51 C 900 o
1 02-JAN-16 52 C 900 o
1 02-JAN-16 53 C 900 o
1 02-JAN-16 54 C 900 o
1 02-JAN-16 55 C 900 o
1 02-JAN-16 56 C 900 o
1 02-JAN-16 57 C 900 o
1 02-JAN-16 58 C 900 o
1 02-JAN-16 59 C 900 o
1 02-JAN-16 60 C 900 o
1 02-JAN-16 61 C 900 o
1 02-JAN-16 62 C 900 o
1 02-JAN-16 63 C 900 o
1 02-JAN-16 64 C 900 o
1 02-JAN-16 65 C 900 o
1 02-JAN-16 66 C 900 o
1 02-JAN-16 67 C 900 o
1 02-JAN-16 68 C 900 o
1 02-JAN-16 69 C 900 o
1 02-JAN-16 70 C 900 o
1 02-JAN-16 71 C 900 o
1 02-JAN-16 72 C 900 o
1 02-JAN-16 73 C 900 o
1 02-JAN-16 74 C 900 o
1 02-JAN-16 75 C 900 o
1 02-JAN-16 76 C 900 o
1 02-JAN-16 77 C 900 o
1 02-JAN-16 78 C 900 o
1 02-JAN-16 79 C 900 o
1 02-JAN-16 80 C 900 o
1 02-JAN-16 81 C 900 o
1 02-JAN-16 82 C 900 o
1 02-JAN-16 83 C 900 o
1 02-JAN-16 84 C 900 o
1 02-JAN-16 85 C 900 o
1 02-JAN-16 86 C 900 o
1 02-JAN-16 87 C 900 o
1 02-JAN-16 88 C 900 o
1 02-JAN-16 89 C 900 o
1 02-JAN-16 90 C 900 o
1 02-JAN-16 91 C 900 o
1 02-JAN-16 92 C 900 o
1 02-JAN-16 93 C 900 o
1 02-JAN-16 94 C 900 o
1 02-JAN-16 95 C 900 o
1 02-JAN-16 96 C 900 o
1 02-JAN-16 97 C 900 o
1 02-JAN-16 98 C 900 o
1 02-JAN-16 99 C 900 o
1 02-JAN-16 100 C 900 o
2 02-JAN-16 1 A 900 o
2 02-JAN-16 2 A 900 o
2 02-JAN-16 3 A 900 o
2 02-JAN-16 4 A 900 o
2 02-JAN-16 5 A 900 o
2 02-JAN-16 6 A 900 o
2 02-JAN-16 7 A 900 o
2 02-JAN-16 8 A 900 o
2 02-JAN-16 9 A 900 o
2 02-JAN-16 10 A 900 o
2 02-JAN-16 11 A 900 o
2 02-JAN-16 12 A 900 o
2 02-JAN-16 13 A 900 o
2 02-JAN-16 14 A 900 o
2 02-JAN-16 15 A 900 o
2 02-JAN-16 16 B 900 o
2 02-JAN-16 17 B 900 o
2 02-JAN-16 18 B 900 o
2 02-JAN-16 19 B 900 o
2 02-JAN-16 20 B 900 o
2 02-JAN-16 21 B 900 o
2 02-JAN-16 22 B 900 o
2 02-JAN-16 23 B 900 o
2 02-JAN-16 24 B 900 o
2 02-JAN-16 25 B 900 o
2 02-JAN-16 26 B 900 o
2 02-JAN-16 27 B 900 o
2 02-JAN-16 28 B 900 o
2 02-JAN-16 29 B 900 o
2 02-JAN-16 30 B 900 o
2 02-JAN-16 31 C 900 o
2 02-JAN-16 32 C 900 o
2 02-JAN-16 33 C 900 o
2 02-JAN-16 34 C 900 o
2 02-JAN-16 35 C 900 o
2 02-JAN-16 36 C 900 o
2 02-JAN-16 37 C 900 o
2 02-JAN-16 38 C 900 o
2 02-JAN-16 39 C 900 o
2 02-JAN-16 40 C 900 o
2 02-JAN-16 41 C 900 o
2 02-JAN-16 42 C 900 o
2 02-JAN-16 43 C 900 o
2 02-JAN-16 44 C 900 o
2 02-JAN-16 45 C 900 o
2 02-JAN-16 46 C 900 o
2 02-JAN-16 47 C 900 o
2 02-JAN-16 48 C 900 o
2 02-JAN-16 49 C 900 o
2 02-JAN-16 50 C 900 o
150 rows selected.
And now a new flight for an existing plane
SQL>
SQL> insert into flight_shedule values (2,2,trunc(sysdate)+1,'s','d',trunc(sysdate)+1);
1 row created.
SQL>
SQL> with
2 seats as (
3 select rownum r
4 from dual
5 connect by level <= 350
6 )
7 select flight_shedule.flight_id,
8 flight_shedule.departure_date,
9 r seat,
10 case
11 when r between start_a and end_a then 'A'
12 when r between start_b and end_b then 'B'
13 when r between start_c and end_c then 'C'
14 end class,900 fare,'o' status
15 from seats,
16 airplane,
17 flight_shedule
18 where flight_shedule.airplane_id=airplane.airplane_id
19 and r <= end_c
20 order by 1,2,3;
FLIGHT_ID DEPARTURE SEAT C FARE S
---------- --------- ---------- - ---------- -
1 02-JAN-16 1 A 900 o
1 02-JAN-16 2 A 900 o
1 02-JAN-16 3 A 900 o
1 02-JAN-16 4 A 900 o
1 02-JAN-16 5 A 900 o
1 02-JAN-16 6 A 900 o
1 02-JAN-16 7 A 900 o
1 02-JAN-16 8 A 900 o
1 02-JAN-16 9 A 900 o
1 02-JAN-16 10 A 900 o
1 02-JAN-16 11 B 900 o
1 02-JAN-16 12 B 900 o
1 02-JAN-16 13 B 900 o
1 02-JAN-16 14 B 900 o
1 02-JAN-16 15 B 900 o
1 02-JAN-16 16 B 900 o
1 02-JAN-16 17 B 900 o
1 02-JAN-16 18 B 900 o
1 02-JAN-16 19 B 900 o
1 02-JAN-16 20 B 900 o
1 02-JAN-16 21 C 900 o
1 02-JAN-16 22 C 900 o
1 02-JAN-16 23 C 900 o
1 02-JAN-16 24 C 900 o
1 02-JAN-16 25 C 900 o
1 02-JAN-16 26 C 900 o
1 02-JAN-16 27 C 900 o
1 02-JAN-16 28 C 900 o
1 02-JAN-16 29 C 900 o
1 02-JAN-16 30 C 900 o
1 02-JAN-16 31 C 900 o
1 02-JAN-16 32 C 900 o
1 02-JAN-16 33 C 900 o
1 02-JAN-16 34 C 900 o
1 02-JAN-16 35 C 900 o
1 02-JAN-16 36 C 900 o
1 02-JAN-16 37 C 900 o
1 02-JAN-16 38 C 900 o
1 02-JAN-16 39 C 900 o
1 02-JAN-16 40 C 900 o
1 02-JAN-16 41 C 900 o
1 02-JAN-16 42 C 900 o
1 02-JAN-16 43 C 900 o
1 02-JAN-16 44 C 900 o
1 02-JAN-16 45 C 900 o
1 02-JAN-16 46 C 900 o
1 02-JAN-16 47 C 900 o
1 02-JAN-16 48 C 900 o
1 02-JAN-16 49 C 900 o
1 02-JAN-16 50 C 900 o
1 02-JAN-16 51 C 900 o
1 02-JAN-16 52 C 900 o
1 02-JAN-16 53 C 900 o
1 02-JAN-16 54 C 900 o
1 02-JAN-16 55 C 900 o
1 02-JAN-16 56 C 900 o
1 02-JAN-16 57 C 900 o
1 02-JAN-16 58 C 900 o
1 02-JAN-16 59 C 900 o
1 02-JAN-16 60 C 900 o
1 02-JAN-16 61 C 900 o
1 02-JAN-16 62 C 900 o
1 02-JAN-16 63 C 900 o
1 02-JAN-16 64 C 900 o
1 02-JAN-16 65 C 900 o
1 02-JAN-16 66 C 900 o
1 02-JAN-16 67 C 900 o
1 02-JAN-16 68 C 900 o
1 02-JAN-16 69 C 900 o
1 02-JAN-16 70 C 900 o
1 02-JAN-16 71 C 900 o
1 02-JAN-16 72 C 900 o
1 02-JAN-16 73 C 900 o
1 02-JAN-16 74 C 900 o
1 02-JAN-16 75 C 900 o
1 02-JAN-16 76 C 900 o
1 02-JAN-16 77 C 900 o
1 02-JAN-16 78 C 900 o
1 02-JAN-16 79 C 900 o
1 02-JAN-16 80 C 900 o
1 02-JAN-16 81 C 900 o
1 02-JAN-16 82 C 900 o
1 02-JAN-16 83 C 900 o
1 02-JAN-16 84 C 900 o
1 02-JAN-16 85 C 900 o
1 02-JAN-16 86 C 900 o
1 02-JAN-16 87 C 900 o
1 02-JAN-16 88 C 900 o
1 02-JAN-16 89 C 900 o
1 02-JAN-16 90 C 900 o
1 02-JAN-16 91 C 900 o
1 02-JAN-16 92 C 900 o
1 02-JAN-16 93 C 900 o
1 02-JAN-16 94 C 900 o
1 02-JAN-16 95 C 900 o
1 02-JAN-16 96 C 900 o
1 02-JAN-16 97 C 900 o
1 02-JAN-16 98 C 900 o
1 02-JAN-16 99 C 900 o
1 02-JAN-16 100 C 900 o
2 02-JAN-16 1 A 900 o
2 02-JAN-16 2 A 900 o
2 02-JAN-16 3 A 900 o
2 02-JAN-16 4 A 900 o
2 02-JAN-16 5 A 900 o
2 02-JAN-16 6 A 900 o
2 02-JAN-16 7 A 900 o
2 02-JAN-16 8 A 900 o
2 02-JAN-16 9 A 900 o
2 02-JAN-16 10 A 900 o
2 02-JAN-16 11 A 900 o
2 02-JAN-16 12 A 900 o
2 02-JAN-16 13 A 900 o
2 02-JAN-16 14 A 900 o
2 02-JAN-16 15 A 900 o
2 02-JAN-16 16 B 900 o
2 02-JAN-16 17 B 900 o
2 02-JAN-16 18 B 900 o
2 02-JAN-16 19 B 900 o
2 02-JAN-16 20 B 900 o
2 02-JAN-16 21 B 900 o
2 02-JAN-16 22 B 900 o
2 02-JAN-16 23 B 900 o
2 02-JAN-16 24 B 900 o
2 02-JAN-16 25 B 900 o
2 02-JAN-16 26 B 900 o
2 02-JAN-16 27 B 900 o
2 02-JAN-16 28 B 900 o
2 02-JAN-16 29 B 900 o
2 02-JAN-16 30 B 900 o
2 02-JAN-16 31 C 900 o
2 02-JAN-16 32 C 900 o
2 02-JAN-16 33 C 900 o
2 02-JAN-16 34 C 900 o
2 02-JAN-16 35 C 900 o
2 02-JAN-16 36 C 900 o
2 02-JAN-16 37 C 900 o
2 02-JAN-16 38 C 900 o
2 02-JAN-16 39 C 900 o
2 02-JAN-16 40 C 900 o
2 02-JAN-16 41 C 900 o
2 02-JAN-16 42 C 900 o
2 02-JAN-16 43 C 900 o
2 02-JAN-16 44 C 900 o
2 02-JAN-16 45 C 900 o
2 02-JAN-16 46 C 900 o
2 02-JAN-16 47 C 900 o
2 02-JAN-16 48 C 900 o
2 02-JAN-16 49 C 900 o
2 02-JAN-16 50 C 900 o
2 03-JAN-16 1 A 900 o
2 03-JAN-16 2 A 900 o
2 03-JAN-16 3 A 900 o
2 03-JAN-16 4 A 900 o
2 03-JAN-16 5 A 900 o
2 03-JAN-16 6 A 900 o
2 03-JAN-16 7 A 900 o
2 03-JAN-16 8 A 900 o
2 03-JAN-16 9 A 900 o
2 03-JAN-16 10 A 900 o
2 03-JAN-16 11 A 900 o
2 03-JAN-16 12 A 900 o
2 03-JAN-16 13 A 900 o
2 03-JAN-16 14 A 900 o
2 03-JAN-16 15 A 900 o
2 03-JAN-16 16 B 900 o
2 03-JAN-16 17 B 900 o
2 03-JAN-16 18 B 900 o
2 03-JAN-16 19 B 900 o
2 03-JAN-16 20 B 900 o
2 03-JAN-16 21 B 900 o
2 03-JAN-16 22 B 900 o
2 03-JAN-16 23 B 900 o
2 03-JAN-16 24 B 900 o
2 03-JAN-16 25 B 900 o
2 03-JAN-16 26 B 900 o
2 03-JAN-16 27 B 900 o
2 03-JAN-16 28 B 900 o
2 03-JAN-16 29 B 900 o
2 03-JAN-16 30 B 900 o
2 03-JAN-16 31 C 900 o
2 03-JAN-16 32 C 900 o
2 03-JAN-16 33 C 900 o
2 03-JAN-16 34 C 900 o
2 03-JAN-16 35 C 900 o
2 03-JAN-16 36 C 900 o
2 03-JAN-16 37 C 900 o
2 03-JAN-16 38 C 900 o
2 03-JAN-16 39 C 900 o
2 03-JAN-16 40 C 900 o
2 03-JAN-16 41 C 900 o
2 03-JAN-16 42 C 900 o
2 03-JAN-16 43 C 900 o
2 03-JAN-16 44 C 900 o
2 03-JAN-16 45 C 900 o
2 03-JAN-16 46 C 900 o
2 03-JAN-16 47 C 900 o
2 03-JAN-16 48 C 900 o
2 03-JAN-16 49 C 900 o
2 03-JAN-16 50 C 900 o
200 rows selected.
SQL>
SQL>
and thats look OK too.
Hope this helps.