求不用with来改写下面的sql

Question

sql

Answer 1

SELECT PATH_LEVEL,
       FULL_PATH,
       NODE_ID,
       NAME,
       PARENT_ID,
       CHANGE_TIME,
       PERFORMER,
       REPOSITORY_ID,
       NODE_TYPE,
       OWNER,
       DOCUMENT_TYPE_ID,
       CONTENT_LENGHT,
       CONTENT_TYPE,
       DOC_COMMENT,
       DOC_TYPE_CODE,
       DOC_TYPE_NAME,
       SOURCE_URL
  FROM (SELECT 0 AS PATH_LEVEL,
               CAST('/' + NAME AS VARCHAR(MAX)) AS FULL_PATH,
               NODE_ID,
               NAME,
               PARENT_ID,
               CHANGE_TIME,
               PERFORMER,
               REPOSITORY_ID,
               NODE_TYPE,
               OWNER,
               DOCUMENT_TYPE_ID,
               CONTENT_LENGHT,
               CONTENT_TYPE,
               DOC_COMMENT,
               DOC_TYPE_CODE,
               DOC_TYPE_NAME,
               SOURCE_URL
          FROM DOCV_NODE
         WHERE PARENT_ID IS NULL
        UNION ALL
        SELECT B.PATH_LEVEL + 1 AS PATH_LEVEL,
               CAST(B.FULL_PATH + '/' + A.NAME AS VARCHAR(MAX)) AS FULL_PATH,
               A.NODE_ID,
               A.NAME,
               A.PARENT_ID,
               A.CHANGE_TIME,
               A.PERFORMER,
               A.REPOSITORY_ID,
               A.NODE_TYPE,
               A.OWNER,
               A.DOCUMENT_TYPE_ID,
               A.CONTENT_LENGHT,
               A.CONTENT_TYPE,
               A.DOC_COMMENT,
               A.DOC_TYPE_CODE,
               A.DOC_TYPE_NAME,
               A.SOURCE_URL
          FROM DOCV_NODE A
         INNER JOIN PATH B
            ON B.NODE_ID = A.PARENT_ID)

Answer 2

with 在9i中就已经支持了，只是10g还不知道with的别名中对字段名的定义
把with path() 这里()部分去掉就Ok了