create view from two tables.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: create view from two tables.

  1. #1
    Join Date
    Nov 2001
    Posts
    15

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    What is not "corrected" about the the result? Could you post the actual output and the expected output?
    Assistance is Futile...

  3. #3
    Join Date
    Nov 2001
    Posts
    15
    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.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  5. #5
    Join Date
    Nov 2001
    Posts
    15
    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.

  6. #6
    Join Date
    Nov 2001
    Posts
    15
    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
  •  


Click Here to Expand Forum to Full Width