1 2 Previous Next 27 Replies Latest reply: Jun 17, 2013 5:13 AM by popovitsj Go to original post RSS
      • 15. Re: Query to find previous Activity in STM
        Frank Kulash
        Hi,
        popovitsj wrote:
        ... I did get a 'Too many arguments exception' btw, but removing the last "1" resolved that...
        The 6th argument to REGEXP_SUBSTR was a new feature in Oracle 11.1. This is one example of why it's important to say which version you're using.
        • 16. Re: Query to find previous Activity in STM
          Cherif bh
          Hi ,

          You have said that table activity contains :

          INSERT INTO Activity VALUES (1,'A');
          INSERT INTO Activity VALUES (2,'B');
          INSERT INTO Activity VALUES (3,'C');
          INSERT INTO Activity VALUES (4,'D');


          I have thought that you have the below graph:

          (1)'A'=>(2) 'B'==>(3) 'C'==>(4) 'D'

          So the id represent the identifier of current position ?
          Is it?
          • 17. Re: Query to find previous Activity in STM
            popovitsj
            Cherif bh wrote:
            Hi ,

            You have said that table activity contains :

            INSERT INTO Activity VALUES (1,'A');
            INSERT INTO Activity VALUES (2,'B');
            INSERT INTO Activity VALUES (3,'C');
            INSERT INTO Activity VALUES (4,'D');


            I have thought that you have the below graph:

            (1)'A'=>(2) 'B'==>(3) 'C'==>(4) 'D'

            So the id represent the identifier of current position ?
            Is it?
            Your representation of the graph is correct, but the sole use of the Id is to identify a row. You can't assume that high id's come after low id's or vice versa.
            • 18. Re: Query to find previous Activity in STM
              popovitsj
              Frank Kulash wrote:
              Hi,
              popovitsj wrote:
              ... I did get a 'Too many arguments exception' btw, but removing the last "1" resolved that...
              The 6th argument to REGEXP_SUBSTR was a new feature in Oracle 11.1. This is one example of why it's important to say which version you're using.
              The version I'm using is 10.2.

              So can I take it that this 6th argument is not important and can be left out for my version?
              • 19. Re: Query to find previous Activity in STM
                Frank Kulash
                Hi,
                popovitsj wrote:
                ... The version I'm using is 10.2.

                So can I take it that this 6th argument is not important and can be left out for my version?
                It depends on your requirements.

                With the 6th argument to REGEXP_SUBSTR, in Oracle 11, the output from this test case is
                PREV_ID
                ----------
                C
                Without the 6th argument, the output is
                PREV_ID
                ----------
                C/D/
                I don't know if that difference is important to you or not.
                If it is important, you can use REGEXP_REPLACE instead of REGEXP_SUBSTR to get exactly what you want:
                WITH     all_paths    AS
                (
                     SELECT     'A' || SYS_CONNECT_BY_PATH (a.Node_id, '/')
                              || '/'     AS node_id_path
                     FROM     Transition  t
                     JOIN     Activity    a  ON  a.Node_Id  = t.ToNode_Id
                     WHERE     LEVEL             = (
                                            SELECT  COUNT (*)
                                         FROM     activity
                                         ) - 1
                     START WITH  t.FromNode_Id  = 'A'
                     CONNECT BY  t.FromNode_Id  = PRIOR t.ToNode_Id
                ) -- Everything up to this point is the same as previously posted
                SELECT       REGEXP_REPLACE ( node_id_path
                                  , '.*/([^/]+)/' || (
                                                         SELECT  Node_Id
                                                   FROM    activity
                                                   WHERE   Id     = 4  -- target_id
                                                    )
                                             || '/.*'
                               , '\1'
                               )     AS prev_id
                FROM       all_paths
                ;
                Edited by: Frank Kulash on May 2, 2013 11:39 AM
                Added code
                • 20. Re: Query to find previous Activity in STM
                  popovitsj
                  Alright, I figured out how it works. It's a lot less complicated than I thought. We basically capitalize on this neat little function SYS_CONNECT_BY_PATH.

                  We assume that only one possible path will be found that is of the same length as the number of rows in Activity. If more than one path is found, the provided data is either invalid or ambigue.

                  Do you reckon that this is possible, e.i. the data being ambigue, while still following all the rules, most notably the rules that prohibit any recursion and circular transitions?
                  • 21. Re: Query to find previous Activity in STM
                    Frank Kulash
                    Hi,
                    popovitsj wrote:
                    Alright, I figured out how it works. It's a lot less complicated than I thought. We basically capitalize on this neat little function SYS_CONNECT_BY_PATH.
                    Exactly! there are many graph problems where we're interested in the a path through the graph, and not merely individual nodes in that path, and SYS_CONNECT_BY_PATH can be extremely helpful.
                    We assume that only one possible path will be found that is of the same length as the number of rows in Activity. If more than one path is found, the provided data is either invalid or ambigue.
                    I'm not sure what you're saying.
                    If the data is valid, it's not ambiguous.
                    If the data is invalid, then it's always ambiguous in the sense that we can't tell what the mistake is; we can only tell that there is some mistake. We can't always tell even if the mistake is in the Activity table, or the Transition table, or both.
                    Do you reckon that this is possible, e.i. the data being ambigue, while still following all the rules, most notably the rules that prohibit any recursion and circular transitions?
                    Sorry, I don't understand the question at all.
                    • 22. Re: Query to find previous Activity in STM
                      popovitsj
                      Alright, I'll try to rephrase.

                      Let's say the data in Activity is valid.

                      Can it still be ambigious in the sense that multiple valid paths are possible?
                      • 23. River Model
                        Frank Kulash
                        Hi,
                        popovitsj wrote:
                        Alright, I'll try to rephrase.

                        Let's say the data in Activity is valid.

                        Can it still be ambigious in the sense that multiple valid paths are possible?
                        No.
                        If your graph has no loops, then it follows what I call the "River Pattern". Think of your nodes as points along a river, which has its source at node A, then flows past B. After B, there's an island in the river: if you take one branch, then you pass C and D (past the downstream end of the island), but if you go on the other side of the island, you go from B to D without passing C. A valid path is one that drifts downstream with the current. So if there is a valid path A/B/C/D, that must mean that A is upstream from B, B is upstream from C, and C is upstream from D. Any path that vistis the same points in any other order must be going against the current at some point, so that other path can't be valid.
                        • 24. Re: River Model
                          popovitsj
                          Okay, thank you. I thought so, but I just wasn't completely sure.
                          • 25. Re: Query to find previous Activity in STM
                            popovitsj
                            Frank, I don't really get the use of REGEXP_REPLACE here.

                            How about this solution?

                            It uses 2 functions instead of 1, but I find it easier to understand. I'm not convinced the REGEXP_REPLACE works in all situations.
                            REGEXP_SUBSTR
                            (
                                 REGEXP_SUBSTR
                                 (
                                      node_id_path, '[^/]+/' ||
                                      (
                                           SELECT  Node_Id FROM activity WHERE Id = 4  -- target_id
                                      )
                                 ),
                                 '[^/]+'
                            ) AS prev_id
                            • 26. Re: Query to find previous Activity in STM
                              Frank Kulash
                              Hi,
                              popovitsj wrote:
                              Frank, I don't really get the use of REGEXP_REPLACE here.
                              In Oracle 10, you can use REGEXP_SUBSTR to return the sub-string that matches a given pattern, but it will return exverything that's part of that pattern's definition. In this case, with taget id 4, the pattern we're looking for is /x/D/
                              (where x is 1 or more non-slashes), but, when we find it, we really only want to retrieve the x part, and there's no good way to do that in Oracle 10 REGEXP_SUBSTR. However, REGEXP_REPLACE can do that: we can add wild-cards at the beginning and end of the pattern to match the entire string, and say "when you find this pattern, replace it with just the x part". I admit it is convoluted; that's why I didn't even suggest it when I thought there was a chanve you could use the Oracle 11 REGEXP_SUBSTR.
                              How about this solution?
                              That looks okay. It will be slower than a single REGEXP_REPLACE, but you probably won't notice. CONNECT BY is going to be the performance bottleneck in this quary, no matter what other things you do.
                              It uses 2 functions instead of 1, but I find it easier to understand.
                              Good point. Since you're going to debug and maintain this code, it's important that you use code that you understand.
                              I'm not convinced the REGEXP_REPLACE works in all situations
                              You're right. The REGEXP_REPLACE solution I posted earlier breaks down near the beginning of the string.
                              If the target id happens to be the 2nd node in the path, it won't find a '/' before the 1st node_id. That's easy to fix: just change the literal 'A' to '/A' at the beginning of node_id_path.
                              If the target_id is the first node in the path, I assume you want to return NULL, since there is no previous node. That's what my REGEXP_SUBSTR solution and your nested REGEXP_SUBSTR soltuions do, but the REGEXP_REPLACE solution doesn't find a previous node, so it doesn't change anything.
                              For the record, here's a REGEXP_REPLACE solution that works even when the target id is the root node of the whole path:
                              WITH     all_paths    AS
                              (
                                   SELECT     '//A' || SYS_CONNECT_BY_PATH (a.Node_id, '/')     -- 'A' changed to '//A'
                                             || '/'     AS node_id_path
                                   FROM     Transition  t
                                   JOIN     Activity    a  ON  a.Node_Id  = t.ToNode_Id
                                   WHERE     LEVEL             = (
                                                          SELECT  COUNT (*)
                                                       FROM     activity
                                                       ) - 1
                                   START WITH  t.FromNode_Id  = 'A'
                                   CONNECT BY  t.FromNode_Id  = PRIOR t.ToNode_Id
                              ) 
                              SELECT     REGEXP_REPLACE ( node_id_path
                                                , '.*/([^/]*)/' || (          -- '+' changed to '*'
                                                                   SELECT  Node_Id
                                                             FROM    activity
                                                             WHERE   Id  = :target_id
                                                                )
                                                               || '/.*'
                                               , '\1'
                                               )     AS prev_id
                              FROM       all_paths
                              ;
                              Of course, your reason for not using the previous REGEXP_REPLACE query applies even more to this one.
                              • 27. Re: Query to find previous Activity in STM
                                popovitsj

                                Frank, I converted your solution to work with our actual database, but my boss isn't convinced that it's the best solution.

                                 

                                At the moment, he is too busy to look into it thoroughly himself, but he claims that creating a string and searching in it makes the solution overly complex.

                                 

                                Do you know what I could say to convince him that this is really necessary, or do you maybe think that he's right and we could solve this problem without concatenating a string?

                                1 2 Previous Next