SQL 整理示範

gistfile1.sql

SELECT
    DISTINCT DECODE(CARNO, NULL, 'X','Y') P       -- 是否有車號, 'X':無, 'Y':有
  , RQT_NO RNO                                    -- 叫車序號
  , RQTSEQ_NO RSNO                                -- 叫車序號支號 
  , DEP                                           -- 通關類別
  , (SELECT SUBSTR(DEP_NAME,1,2) FROM ORG WHERE ORG.DEP_ID = T.DEP_ID) DEP_ID   -- 部門名稱前2碼
  , RQT_BY
  , DECODE(BOX_NOCAR,NULL, CARNO,CARNO||','||BOX_NOCAR) CARNO
  , NVL((SELECT CEMP_NAME FROM EMPLOYEE WHERE EMP_CODE= T.DRIVER),DRIVER)DR
  , CASE WHEN RESERVED_FLAG = 'Y' THEN            -- 若是專車
            (SELECT DESCP FROM TRNUNT 
             WHERE FLAG = '6' 
               AND CODE = SUBSTR(TRUCK_TON,1,2)
            ) ||
            (SELECT DESCP FROM TRNUNT
             WHERE FLAG = '7' 
               AND CODE = TRUCK_TON
            )
        ELSE 
            '併' 
        END 
    TON
  , PICKUP_TIME PT
  , nvl(SELECT SUBSTR(AREA,1,2) FROM BSPORT WHERE ZONE = PICKUP_ZIP)
        , PICKUP_ZIP)
    SZ
  , nvl((SELECT SUBSTR(AREA,1,2) FROM BSPORT WHERE ZONE = DLV_ZIP)
        , DLV_ZIP)
    EZ
  , TO_CHAR(REMARK) RM
FROM TM2 T