Hierarchical Query with clause "START WITH" not work when use IN subquery as condition whe-CodePudding

I have query to find any member of team that below the group that lead by one upper level group of target employee, and test at DBeaver using query like below

WITH HIERARCHICAL_TB AS (
   SELECT 
      LEVEL as "XLEVEL"
      , t.EMP_ID as "EMP_ID"
      , t.NAME
      , t.JOB_POS_ID as "JOB_CD"
      , t.TEAM_ID
      , t.TEAM_PARENT_ID
      , 'TEAM_TREE' as "FOUND_AT"
      , PRIOR t.EMP_ID as "PRIOR_EMP_ID
      , PRIOR t.TEAM_ID as "PRIOR_TEAM_ID
      , PRIOR t.TEAM_PARENT_ID as "PRIOR_TEAM_PARENT_ID
   FROM 
      TB_EMPLOYEE t
      START WITH
         t.TEAM_PARENT_ID IN (
            SELECT 
               b.TEAM_PARENT_ID 
            FROM 
               TB_EMPLOYEE b 
            WHERE 
               b.EMP_ID IN (:TARGET_EMP)
         )
      CONNECT BY NOCYCLE
         PRIOR TEAM_ID = TEAM_PARENT_ID
         AND PRIOR EMP_ID <> EMP_ID
   )
SELECT 
   count(*)
FROM
   SELECT
      ht.XLEVEL
      , ht.EMP_ID
      , ht.NAME
      , ht.JOB_CD
      , ht.PRIOR_EMP_ID as "LEAD_EMP_ID"
   FROM
      HIERARCHICAL_TB ht
   WHERE
      ht.NAME like (':SEARCH' || '%')

this query work fine when run at DBeaver with correct amount number as result but... when I use same query in code that using JdbcTemplate the result is zero.

but when I replace subquery with the static value like this

 WITH HIERARCHICAL_TB AS (
   SELECT 
      LEVEL as "XLEVEL"
      , t.EMP_ID as "EMP_ID"
      , t.NAME
      , t.JOB_POS_ID as "JOB_CD"
      , t.TEAM_ID
      , t.TEAM_PARENT_ID
      , 'TEAM_TREE' as "FOUND_AT"
      , PRIOR t.EMP_ID as "PRIOR_EMP_ID
      , PRIOR t.TEAM_ID as "PRIOR_TEAM_ID
      , PRIOR t.TEAM_PARENT_ID as "PRIOR_TEAM_PARENT_ID
   FROM 
      TB_EMPLOYEE t
      START WITH
         t.TEAM_PARENT_ID IN ('123456','345678')
      CONNECT BY NOCYCLE
         PRIOR TEAM_ID = TEAM_PARENT_ID
         AND PRIOR EMP_ID <> EMP_ID
   )
SELECT 
   count(*)
FROM
   SELECT
      ht.XLEVEL
      , ht.EMP_ID
      , ht.NAME
      , ht.JOB_CD
      , ht.PRIOR_EMP_ID as "LEAD_EMP_ID"
   FROM
      HIERARCHICAL_TB ht
   WHERE
      ht.NAME like (':SEARCH' || '%')

the second query got same result from DBerver and JdbcTemplate.

the problem is: why the first example query got difference result between DBeaver and JdbcTemplate because the real query is more complex than these examples and I would like to use statement that can proof result from query tool before put it in to the code.

I use ojdbc8.jar as jdbc driver, it same as I use at DBeaver (v6.3.0) and my project using spring-boot-starter-jdbc:2.2.2.RELEASE

CodePudding user response：

the problem is this query (as @AlexPoole make an observation)

SELECT 
   b.TEAM_PARENT_ID 
FROM 
   TB_EMPLOYEE b 
WHERE 
   b.EMP_ID IN (:TARGET_EMP)

the cause of problem is b.EMP_ID in table TB_EMPLOYEE contain white space, and got no result at JdbcTemplate but at DBeaver have result (I still don't understand why DBeaver got result), so I fix the query by turn b.EMP_ID IN (:TARGET_EMP) to be trim(b.EMP_ID IN (:TARGET_EMP).

Thank you for all help.