公共表表达式

      公共表表达式是在SQL语句中可被多次引用的本地临时表,只能在使用它的SQL语句执行期存在.

WITH
    T1(TABSCHEMA,TABNAME,DEFINER) AS (SELECT CREATOR,NAME,DEFINER FROM SYSIBM.SYSTABLES WHERE TYPE='N'),
    T2(TABSCHEMA,TABNAME,OPTION,REMOTE_SERVER) AS (SELECT TABSCHEMA,TABNAME,OPTION, CAST(SUBSTR(SETTING, 1, 255) AS VARCHAR(255))
    FROM SYSCAT.TABOPTIONS WHERE OPTION='SERVER'), T3 (WRAPNAME, SERVERNAME, SER
VERTYPE, SERVERVERSION) AS (SELECT WRAPNAME,
    SERVERNAME, SERVERTYPE, SERVERVERSION FROM SYSIBM.SYSSERVERS), T4 (LIBRARY,
WRAPNAME) AS (SELECT LIBRARY, WRAPNAME FROM
    SYSIBM.SYSWRAPPERS) SELECT A.TABSCHEMA,A.TABNAME,A.DEFINER,B.REMOTE_SERVER,C
.WRAPNAME,D.LIBRARY, C.SERVERNAME, C.SERVERTYPE,
    C.SERVERVERSION  FROM T1 A,T2 B, T3 C, T4 D WHERE A.TABSCHEMA=B.TABSCHEMA  A
ND A.TABNAME=B.TABNAME  AND B.REMOTE_SERVER =
    C.SERVERNAME  AND C.WRAPNAME = D.WRAPNAME AND B.REMOTE_SERVER='<servername>' ORDER
BY A.TABSCHEMA,A.TABNAME FOR FETCH ONLY WITH UR