Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2008
    Posts
    8

    Question Unanswered: Range Overlap question

    I have a table that has columns [s_val] and [e_val], but there is a possibility that they overlap.
    I need a result set that merges these rows into a bigger time range.

    For exampe:

    CREATE TABLE overlap_test (id NUMBER(1,0), s_val NUMBER(2), e_val NUMBER(2));
    INSERT INTO overlap_test VALUES (1, 1, 4);
    INSERT INTO overlap_test VALUES (1, 3, 6);
    INSERT INTO overlap_test VALUES (1, 5, 8);
    INSERT INTO overlap_test VALUES (1, 9, 10);

    So the results should be:
    Id Sv Ev
    =========
    1, 1, 8
    1, 9, 10
    =========

    I have been playing around with the LAG/LEAD function, but I'm not entirely sure if this is the way to go.
    Currently, I am only able to get the adjacent row.

    SELECT
    id,
    s_val,
    CASE WHEN (LEAD(s_val) OVER (PARTITION BY id ORDER BY s_val)) BETWEEN s_val AND e_val THEN (
    CASE WHEN (LEAD(e_val) OVER (PARTITION BY id ORDER BY s_val)) > e_val
    THEN (LEAD(e_val) OVER (PARTITION BY id ORDER BY s_val)) ELSE e_val END
    ) ELSE e_val END e_val
    FROM overlap_test

    Id Sv Ev
    =========
    1, 1, 6
    1, 3, 8
    1, 5, 8
    1, 9, 10
    =========

    Any ideas?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You need to keep in mind that rows in a table have NO inherent order & are not necessarily returned in the order in which they are inserted.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2008
    Posts
    8
    I am also currently looking at the CONNECT BY command, but the examples that I see already have the parent-child relationship established. They also usually have "START WITH" but they put hardcoded values in their examples...

    The LEAD/LAG query I posted doesn't work now that I tried putting more sample data.. Is there a way to look for a record that matches some condition instead of only checking to see if the previous/next record matches the condition?
    Last edited by sikidhart; 04-07-09 at 23:01.

  4. #4
    Join Date
    Apr 2008
    Posts
    8

    Got It!!!

    SELECT id, MIN(s_val), MAX(e_val)
    FROM (
    SELECT row_id, id, s_val, e_val, CONNECT_BY_ROOT row_id parent_id, level
    FROM (
    SELECT
    t1.*,
    t2.row_id parent_id,
    t2.s_val parent_s_val,
    t2.e_val parent_e_val
    FROM (
    SELECT ROWNUM row_id, id, s_val, e_val FROM (
    SELECT * FROM overlap_test ORDER BY id, s_val, e_val
    )
    ) t1
    LEFT OUTER JOIN (
    SELECT ROWNUM row_id, id, s_val, e_val FROM (
    SELECT * FROM overlap_test ORDER BY id, s_val, e_val
    )
    ) t2
    ON t1.id = t2.id AND t2.row_id < t1.row_id
    AND t2.e_val >= t1.s_val
    )
    START WITH parent_id IS NULL
    CONNECT BY PRIOR row_id = parent_id
    ) GROUP BY id, parent_id


    but it looks really ugly...
    follow up question though...

    does anybody know if the subqueries t1 and t2 in my statement (which are exactly the same) would be executed 2x? if so, is there a better way to do things? the actual query that i need to put inside t1 and t2 is quite heavy as it its.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Congratulations!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Apr 2008
    Posts
    8

    Red face better way?

    is there a better/faster way of doing this?
    i was thinking of maybe creating temporary tables...?
    or just do the whole thing in java?

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    I'd use PL/SQL. Unless ... is it possible that you can roll these up into single records upon insert/update, so that you don't have to write a FUNCTION? Or do these records have to remain independent, and then have their overlap determined at runtime? I could see the use of a BEFORE STATEMENT TRIGGER (BST) and AFTER STATEMENT TRIGGER (AST) to make this happen, maybe with a PACKAGE SPEC variable to track primary key values (in the BST, look for an overlapping range, alter the record being inserted, and store the PK of the other range record in the PACKAGE SPEC, then in the AST, delete that other record).

    With PL/SQL, you can write code which returns rows via a PIPELINED feature, effectively allowing SQL to call the FUNCTION and return data:

    Code:
    SQL> CREATE OR REPLACE PACKAGE XXNNNN_PG AS
      2  --
      3  TYPE RETURN_ROW is RECORD (ID       NUMBER(10),
      4                             EMP_NAME VARCHAR2(10)
      5                            );
      6          
      7  -- create a PL/SQL table capable of holding that row
      8  --
      9  TYPE RETURN_TAB is TABLE of RETURN_ROW;       
     10  
     11  -- create the main function with which the calling application will interface
     12  -- with, making sure that the return variable is of the PL/SQL table type
     13  -- defined above, and set to be PIPELINED
     14  --
     15  FUNCTION Main(Arg_Report  IN VarChar2) return RETURN_TAB PIPELINED;
     16  
     17  END XXNNNN_PG;
     18  /
    
    Package created.
    SQL> CREATE OR REPLACE PACKAGE BODY XXNNNN_PG AS
      2  
      3    OUTPUT_ROW        RETURN_ROW;
      4  
      5  -----------------------------------------
      6  -----------------------------------------
      7    FUNCTION Main(Arg_Report  IN VarChar2) return RETURN_TAB PIPELINED IS
      8    BEGIN
      9  
     10      IF Arg_Report = 'A' Then
     11        output_row.ID := '1';
     12        output_row.emp_name := 'Chuck';
     13      ELSIF Arg_Report = 'B' Then
     14        output_row.ID := '2';
     15        output_row.emp_name := 'Charlie';
     16      ELSE
     17        output_row.ID := '3';
     18        output_row.emp_name := 'Charles';
     19      END IF;
     20  
     21  
     22      PIPE ROW(OUTPUT_ROW);
     23    
     24      --Must be the last line 
     25      RETURN;
     26  
     27    END Main;
     28  -----------------------------------------
     29  END XXNNNN_PG;
     30  /
    
    Package body created.
    SQL> select * from TABLE(XXNNNN_PG.MAIN('A'));
    
            ID EMP_NAME
    ---------- ----------
             1 Chuck
    
    SQL> select * from TABLE(XXNNNN_PG.MAIN('B'));
    
            ID EMP_NAME
    ---------- ----------
             2 Charlie
    
    SQL> select * from TABLE(XXNNNN_PG.MAIN('C'));
    
            ID EMP_NAME
    ---------- ----------
             3 Charles
    --=Chuck

  8. #8
    Join Date
    Apr 2008
    Posts
    8
    Yeah, modifying them at insert time would probably be the most logical way to go about it...
    I'm not familiar with PL/SQL functions...
    I was thinking along these lines... but I just can't get the UPDATE SET part to work... any ideas?

    Code:
    MERGE INTO overlap_test_2 t2 
    USING (SELECT * FROM overlap_test) t1
    	ON (t1.id = t2.id AND t1.s_val < t2.e_val AND t1.e_val > t2.s_val)
    	WHEN MATCHED THEN 
    		UPDATE SET 
    			t2.s_val = // the smaller value between t1.s_val AND t2.s_val
    			t2.e_val = // the bigger value between t1.e_val AND t2.e_val
    	WHEN NOT MATCHED THEN
    		INSERT (t2.id, t2.s_val, t2.e_val)
    		VALUES (t1.id, t1.s_val, t1.e_val)
    Last edited by sikidhart; 04-08-09 at 21:17.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    t2.s_val = MIN( t1.s_val , t2.s_val)
    t2.e_val = MAX(t1.e_val , t2.e_val)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Apr 2008
    Posts
    8
    Tried the MIN/MAX:
    ORA-00934: group function is not allowed here.

    Also tried putting them in parentheses:
    ORA-01747: invalid user.table.column, table.column, or columns specification.

    i tried renaming the ID column too, I still get ORA-01747

    --edit--
    Apparently something is wrong with my query... even t2.s_val = t1.s_val gives the same error...

    --edit--
    Fixed the query but now I'm getting ORA-38104: Columns referenced in the ON Clause cannot be updated
    there goes that idea....
    Last edited by sikidhart; 04-08-09 at 23:16.

Posting Permissions

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