create view from two tables.

[bvo]

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

[waitecj]

What is not "corrected" about the the result? Could you post the actual output and the expected output?

[bvo]

the problem is those fields from table 2 (flight_vfr) should be null if those fields are in table2 but not in table1.

When I checked the view table some fields from table 2 still contain some data.

[tamilselvan]

How to ask a question:
1. Include create table statement, PK etc
2. Include INSERT statements for the table
3. Include output rows.
4. Include Business rules
5. Include your own SQL statement

Tamil
www.oracleact.com

[bvo]

I don't understand your input here?
I just want to create a view table from two tables, why should I need to insert and select primary key, etc...?

[bvo]

That's ok... I get it work. Thanks

bv