create view from two tables.
Hi,
Would you please help me to create a view from two tables? I have two main tables below,
TRACK_VFR (table 1) has
track_index, acid, start_time, dep_aprt, end_time, arr_aprt, abc,
acft_equip, acft_type, ads, commpression, maxdisterr, cnt, trackpoints,
source, flight_index, num_acft, tcas_heavy, and flight_rule
FLIGHT_VFR (table 2) has
FID, acid, act_date, start_time, dep_aprt, end_time, arr_aprt, acft_equip,
acft_type, trackpoints, num_acft, tcas_heavy, compression, cnt, maxdisterr,
arr_aprt_assigned, runway_assigned, runway1_score, runway2_score, on_time,flight_rule, physical_class and user_class.
Use these rules to cover the missing fields:
1) If the field is NOT in FLIGHT_VFR, ignore it.
2) If the field is in FLIGHT_VFR, but not in TRACK_VFR, include it with
NULL values
3) FID=track_vfr.flight_index
4) ACT_DATE=to_date(to_char(start_time,'YYYYMMDD'),'YYYYMMDD')
How can create it? I have tried
"
Create view flight_test as
(select a.acid, a.start_time, a.dep_aprt, a.end_time, a.arr_aprt, a.abc,
a.acft_equip, a.acft_type, a.ads, a.commpression, a.maxdisterr, a.cnt, a.trackpoints, a.num_acft, a.flight_index, a.tcas_heavy, a.flight_rule, b.act_date, b.arr_aprt_assigned, b.runway_assigned, b.runway1_score, b.runway2_score, b.on_time, b.physical_class, b.user_class
FROM track_vfr a, flight_vfr b
WHERE a.flight_index(+) = b.FID
AND b.act_date=to_date(to_char(start_time,'yyyymmdd'), 'yyyymmdd')
);
but the result is not corrected... any helps would appreciated
BV