[Harvest] regexp_substr 를 이용한 디렉토리 별, 확장자 별 파일목록 추출 쿼리

  SELECT   REGEXP_SUBSTR (REGEXP_SUBSTR (tbpat.pathfullname,
                                         '[^\]*[^\]',
                                         1,
                                         3), '[^_]+')
              ban,
           REGEXP_SUBSTR (REGEXP_SUBSTR (tbpat.pathfullname,
                                         '[^\]*[^\]',
                                         1,
                                         4), '[^_]+')
              upmu,
           REGEXP_SUBSTR (REGEXP_SUBSTR (tbpat.pathfullname,
                                         '[^\]*[^\]',
                                         1,
                                         5), '[^_]+')
              server,
           REGEXP_SUBSTR (tbinm.itemnameupper, '\.[^.]*$') ext,
           COUNT (tbinm.nameobjid) itemcount
    FROM   (  SELECT   tbitm_f.itemobjid, MAX (e.versionobjid) versionobjid
                FROM   (SELECT   a.itemobjid
                          FROM   haritems a
                         WHERE   a.repositobjid =
                                    (SELECT   repositobjid
                                       FROM   harrepository
                                      WHERE   repositname = '?') 
                                 AND a.itemtype = 1
                        MINUS
                        SELECT   c.itemobjid
                          FROM   haritems c, harversions d
                         WHERE   c.repositobjid =
                                    (SELECT   repositobjid
                                       FROM   harrepository
                                      WHERE   repositname = '?')
                                 AND c.itemtype = 1
                                 AND c.itemobjid = d.itemobjid
                                 AND d.versionstatus = 'D') tbitm_f, 
                       harversions e
               WHERE   tbitm_f.itemobjid = e.itemobjid
            GROUP BY   tbitm_f.itemobjid) tbver_f, 
           harversions tbver,
           haritemname tbinm,
           harpathfullname tbpat
   WHERE       tbver_f.versionobjid = tbver.versionobjid
           AND tbver.itemnameid = tbinm.nameobjid
           AND tbver.pathversionid = tbpat.versionobjid
GROUP BY   REGEXP_SUBSTR (REGEXP_SUBSTR (tbpat.pathfullname,
                                         '[^\]*[^\]',
                                         1,
                                         3), '[^_]+'),
           REGEXP_SUBSTR (REGEXP_SUBSTR (tbpat.pathfullname,
                                         '[^\]*[^\]',
                                         1,
                                         4), '[^_]+'),
           REGEXP_SUBSTR (REGEXP_SUBSTR (tbpat.pathfullname,
                                         '[^\]*[^\]',
                                         1,
                                         5), '[^_]+'),
           REGEXP_SUBSTR (tbinm.itemnameupper, '\.[^.]*$')
ORDER BY   REGEXP_SUBSTR (REGEXP_SUBSTR (tbpat.pathfullname,
                                         '[^\]*[^\]',
                                         1,
                                         3), '[^_]+'),
           REGEXP_SUBSTR (REGEXP_SUBSTR (tbpat.pathfullname,
                                         '[^\]*[^\]',
                                         1,
                                         4), '[^_]+'),
           REGEXP_SUBSTR (REGEXP_SUBSTR (tbpat.pathfullname,
                                         '[^\]*[^\]',
                                         1,
                                         5), '[^_]+'),
           REGEXP_SUBSTR (tbinm.itemnameupper, '\.[^.]*$')