PROMPT Creating view onward_flight_v1

CREATE OR REPLACE FORCE VIEW onward_flight_v1
(flight_id
,pax_id
,onward_flight_no_1
,onward_flight_date_1
,onward_destination_1
,onward_class_1
,onward_flight_no_2
,onward_flight_date_2
,onward_destination_2
,onward_class_2
,onward_flight_no_3
,onward_flight_date_3
,onward_destination_3
,onward_class_3
,onward_flight_no_4
,onward_flight_date_4
,onward_destination_4
,onward_class_4
,onward_flight_no_5
,onward_flight_date_5
,onward_destination_5
,onward_class_5
) AS
SELECT flight_id
,pax.pax_id
,MAX(DECODE(dest_order,1,no,NULL))
,MAX(DECODE(dest_order,1,odate,NULL))
,MAX(DECODE(dest_order,1,dest,NULL))
,MAX(DECODE(dest_order,1,class,NULL))
,MAX(DECODE(dest_order,2,no,NULL))
,MAX(DECODE(dest_order,2,odate,NULL))
,MAX(DECODE(dest_order,2,dest,NULL))
,MAX(DECODE(dest_order,2,class,NULL))
,MAX(DECODE(dest_order,3,no,NULL))
,MAX(DECODE(dest_order,3,odate,NULL))
,MAX(DECODE(dest_order,3,dest,NULL))
,MAX(DECODE(dest_order,3,class,NULL))
,MAX(DECODE(dest_order,4,no,NULL))
,MAX(DECODE(dest_order,4,odate,NULL))
,MAX(DECODE(dest_order,4,dest,NULL))
,MAX(DECODE(dest_order,4,class,NULL))
,MAX(DECODE(dest_order,5,no,NULL))
,MAX(DECODE(dest_order,5,odate,NULL))
,MAX(DECODE(dest_order,5,dest,NULL))
,MAX(DECODE(dest_order,5,class,NULL))
FROM onward_flight, pax
WHERE pax.pax_id = onward_flight.pax_id
GROUP BY flight_id
,pax.pax_id
/
