-
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
-
What is not "corrected" about the the result? Could you post the actual output and the expected output?
Assistance is Futile...
-
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.
-
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
-
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...?
Last edited by bvo; 02-26-2007 at 04:02 PM.
-
That's ok... I get it work. Thanks
bv
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|