Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: merge script

  1. #1
    Join Date
    May 2004
    Posts
    29

    Unanswered: merge script

    Hi,
    I want to get data from a table merge itself....in order of one. I am able to select and to make it sort but do not know how to make it merge those data by itself. Any one has clues how can i specify a procedure script? Many thanks...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    ??? An example of input and required output would help me to understand this better!

  3. #3
    Join Date
    May 2004
    Posts
    29

    Post

    the data came in each day w/o an order... with different sector/tracks. If make it sort by some attributes, I know how to do it... but make all tracks/sectors join in together and then sort by the time... I have no clues how...

    For example (not sure..) a table had:
    Flight track_index start_time end_time dep_aprt arr_aprt
    123 132 20040910:9:00 15:00 IAD LAX
    124 130 20040111:10:00 16:00 LAX IAD
    123 129 20040910:15:00 16:00 IAD CHI
    154 132 20040213:14:00 20:00 SJS CAD
    123 113 20040910:6:00 7:00 IAD NY

    The result should be:
    Flight track_index start_time end_time dep_aprt arr_aprt
    124 130 20040111:10:00 16:00 LAX IAD
    154 132 20040213:14:00 20:00 SJS CAD
    123 113 20040910:6:00 7:00 IAD NY
    123 132 20040910:9:00 15:00 IAD LAX
    123 129 20040910:15:00 16:00 IAD CHI

    The problem is... I need to merge it w/in a table and the data is kept coming....on the table

    Thanks for your reply and help

  4. #4
    Join Date
    May 2004
    Posts
    29
    The result should be the data below with the rest of data merged in one with the same flight:
    Flight track_index start_time end_time dep_aprt arr_aprt tck_point
    124 130 20040111:10:00 16:00 LAX IAD -75.9808
    154 132 20040213:14:00 20:00 SJS CAD -90.0988
    123 113 20040910:6:00 7:00 IAD NY -75.9808
    20040910:9:00 15:00 IAD LAX -74.0888
    20040910:15:00 16:00 IAD CHI -80.7080

  5. #5
    Join Date
    May 2004
    Posts
    29
    I have a script here but do not know why it didn't compile, error on END Loop and END package body, MERGE_TRACK . Can somone help me, please???

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Perhaps, if you post your code and the actual error messages.

  7. #7
    Join Date
    May 2004
    Posts
    29
    the errors I had:
    Line # = 73 Column # = 9 Error Text = PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: if
    Line # = 78 Column # = 5 Error Text = PLS-00103: Encountered the symbol "MERGE" when expecting one of the following: ;

    Thanks

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Thanks. Now if we can just see the actual code you were trying to compile...?

  9. #9
    Join Date
    May 2004
    Posts
    29
    You didn't see the file uploaded?
    I corrected and now I got the other and still don't understand why.

    SQL> desc track;
    Name Null? Type
    ----------------- -------- ------------
    TRACK_INDEX NOT NULL VARCHAR2(22)
    ACID VARCHAR2(7)
    START_TIME DATE
    DEP_APRT VARCHAR2(4)
    END_TIME DATE
    ARR_APRT VARCHAR2(4)
    ABC VARCHAR2(5)
    ACFT_EQUIP VARCHAR2(5)
    ACFT_TYPE VARCHAR2(5)
    ADS VARCHAR2(5)
    COMPRESSION NUMBER
    MAXDISTERR NUMBER
    CNT NUMBER
    TRACKPOINTS MDSYS.SDO_GE
    OMETRY
    SOURCE VARCHAR2(3)
    FLIGHT_INDEX NUMBER
    NUM_ACFT NUMBER
    TCAS_HEAVY VARCHAR2(1)

    SQL>

    Script: merge_track.sql:

    set heading off
    set pagesize 0
    set feedback off
    set linesize 8

    spool c:\merge_track.lst


    CREATE OR REPLACE PACKAGE MERGE is

    PROCEDURE merge_track(
    in_julian_date varchar2);

    end MERGE;
    /
    -- **********************************************
    -- * Package Body Area *
    -- **********************************************
    Create or Replace Package Body MERGE is


    PROCEDURE merge_track(in_julian_date varchar2) is
    -- theStartTime date;
    -- theDepAprt varchar2(4);
    -- theArrAprt varchar2(4);
    -- theABC varchar2(5);
    -- theTrackPoint sdo_geometry;
    -- theEndTime date;

    tck varchar2(25);

    tpcnt integer;
    cnt integer;

    CURSOR tck_cur is
    select distinct acid, track_index, start_time, end_time
    from track where start_time=to_date(in_julian_date, 'YYYYMMDD');

    tck_rec tck_cur%ROWTYPE;


    -- You would generally join a table to itself when you have a hierarchy
    -- in the table. Take the TRACK table, you have all information of the Track.

    BEGIN

    OPEN tck_cur;
    FETCH tck_cur INTO tck_rec;
    --Make sure the file still exists

    Select acid into tck from track where start_time=to_date(in_julian_date,'yyyymmdd');

    WHILE tck_cur%FOUND
    LOOP
    IF (tck_rec.dep_aprt=dep_aprt) AND (tck_rec.arr_aprt=arr_aprt) then

    -- IF (tck_rec.dep_aprt=dep_aprt) AND (tck_rec.arr_aprt=NULL) then

    -- IF (tck_rec.arr_aprt=arr_aprt) AND (tck_rec.dep_aprt=NULL) then

    -- IF (tck_rec.beacon_code = beacon_code) AND
    -- (tck_rec.dep_aprt = dep_aprt) OR (tck_rec.dep_aprt='null') OR
    -- (dep_aprt='null') OR (tck_rec.dep_aprt = substr(dep_aprt,2,3)) OR
    -- (dep_aprt = substr(tck_rec.dep_aprt,2,3)) AND
    -- (tck_rec.arr_aprt = arr_aprt) OR (tck_rec.arr_aprt='null') OR
    -- (arr_aprt='null') OR (tck_rec.arr_aprt = substr(arr_aprt,2,3)) OR
    -- (arr_aprt = substr(tck_rec.arr_aprt,2,3)) AND
    -- (tck_rec.dep_aprt = dep_aprt) OR (tck_rec.dep_aprt= 'null') OR
    -- (dep_aprt='null') or (tck_rec.dep_aprt = substr(dep_aprt,2,3)) OR
    -- (dep_aprt = substr(tck_rec.dep_aprt,2,3)) then
    -- update track set acid= (select tck_rec.ACID
    -- from tck_rec where tck_rec.ACID = acid order by tck_rec.start_time);

    merge_track(acid, track_index, start_time, track_point, end_time);
    -- END IF;
    -- END IF;
    -- END IF;
    END IF;

    Commit;

    -- update flight set
    -- dep_aprt=tck_rec.dep_aprt,
    -- arr_aprt=tck_rec.arr_aprt,
    -- trackpoints=tck_rec.trackpoint,
    -- start_time=tck_rec.start_time,
    -- end_time=tck_rec.End_Time,
    -- ABC=tck_rec.ABC
    -- where acid=tc_rec.acid;


    COMMIT;
    FETCH tck_cur INTO tck_rec;

    END LOOP;

    CLOSE tck_cur;
    END merge_track;

    end MERGE;
    /
    show errors
    /
    commit;


    When I compiled it.. I got:
    Line # = 39 Column # = 17 Error Text = PLS-00302: component 'DEP_APRT' must be declared
    Line # = 39 Column # = 5 Error Text = PL/SQL: Statement ignored

    on Track table had DEP_APRT... why I need to declared?
    Thanks much for helping

    BVO

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I can't see any attachment on your previous post - can you?

    Look at this:

    IF (tck_rec.dep_aprt=dep_aprt)

    What is dep_aprt? Did you mean theDepAprt?

  11. #11
    Join Date
    May 2004
    Posts
    29
    but theDepAprt was comment out...
    I want to compare the cursor with the exit table dep_aprt. How can I do that?

    Thanks.

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oh no it wasn't! Look again, just after the LOOP...

  13. #13
    Join Date
    May 2004
    Posts
    29
    I don't see theDepAprt existed... I did comment it out before LOOP

  14. #14
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Quote Originally Posted by bvo
    I don't see theDepAprt existed... I did comment it out before LOOP

    IF (tck_rec.dep_aprt=dep_aprt)


    What is the dep_aprt after the equal sign? If it is a variable, it has not been declared, or did you intend tck_cur.dep_arpt?

  15. #15
    Join Date
    May 2004
    Posts
    29
    How can I make it fill out for me if the cursor dep_aprt = dep_aprt from the table the merge/update the table. Can I make it:
    If (tck_rec.dep_aprt=track.dep_aprt) or not?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •