联表+分页+条件查询+排序+case when的使用 sql

SELECT
    * 
FROM
    (
SELECT
    TMP_PAGE.*,
    ROWNUM ROW_ID 
FROM
    (
SELECT
    z.BOX_ID AS BOX_ID,
    ( CASE WHEN p.ZC_UUID IS NOT NULL THEN p.BOX_CODE ELSE z.BOX_CODE END ) AS BOX_CODE,
    ( CASE WHEN p.ZC_UUID IS NOT NULL THEN p.CONTROL_WAY ELSE z.CONTROL_WAY END ) AS CONTROL_WAY,
    ( CASE WHEN p.ZC_UUID IS NOT NULL THEN p.EXTERELC ELSE z.EXTERELC END ) AS EXTERELC,
    ( CASE WHEN p.ZC_UUID IS NOT NULL THEN p.IS_SHARE ELSE z.IS_SHARE END ) AS IS_SHARE,
    ( CASE WHEN p.DELFLAG = ‘1‘ THEN ‘2‘ WHEN p.ZC_UUID IS NOT NULL THEN ‘1‘ ELSE ‘0‘ END ) AS MEMO,
    (
CASE
    WHEN p.UPDATE_TIME IS NOT NULL THEN
    p.UPDATE_TIME 
    WHEN z.UPDATE_TIME IS NOT NULL THEN
    z.UPDATE_TIME 
    WHEN z.CREATE_TIME IS NOT NULL THEN
    z.CREATE_TIME 
    when  z.CREATE_TIME is null then
     to_date(‘0001-01-01‘,‘yyyy-MM-dd‘)
END 
    ) AS time 
FROM
    USR_ZC.T_ZC_BOX z
    LEFT JOIN USR_ZC.T_PC_BOX p ON z.BOX_ID = p.ZC_UUID 
WHERE
    1 = 1 
ORDER BY
    time DESC 
    ) TMP_PAGE 
WHERE
ROWNUM <= 12 ) WHERE ROW_ID > 0;