How To make a branch Connect by's condition in Sql

Question

Hi I am trying to make a query which draws the full call-graph of a code.

the table's columns are caller class, caller method, callee class, callee method

but some of callee classes starts with un-necessery words. so when it detected, I have to erase them to use connect by prior.

one more thing. a few callee method column's value contains just method's name without parameter.

What I am trying to do sql is

SELECT 
    caller_class, caller_method, callee_class, callee_method
FROM 
    call_rel
START WITH 
    caller_class ='test.callee.class'
    AND
callee_method = 'test(java.lang.Object arg0)'

CONNECT BY NOCYCLE
PRIOR callee_class = caller_class 
AND 
PRIOR
    CASE WHEN INSTR(callee_method, '(', 1, 1) > 0 --this means method name only
    THEN callee_method LIKE CONCAT(caller_method, '%')
    ELSE callee_method = caller_method
    END;

db says this query has a error.

What I want to do is that if condition matches, use equals or not, use like as a prior condition.

How should I fix this sql query...

Answer 1

You can't use a case expression to decide which conditions to apply. You can use boolean logic with AND and OR though.

You seem to want something like:

PRIOR callee_class = caller_class 
AND (
  (
    INSTR(callee_method, '(', 1, 1) > 0
    AND PRIOR callee_method LIKE CONCAT(caller_method, '%')
  )
  OR
  (
    INSTR(callee_method, '(', 1, 1) = 0
    AND PRIOR callee_method = caller_method
  )
);

I've split the conditions into multiple lines and indented to try to show the logic more clearly.

The first line is from your original code and is always applied. Then the first AND means the next condition has to be true as well. That second condition is actually a compound: either the callee_method has a parenthesis and the like is true; or the callee_method does not have a parenthesis and the = is true.