關於CASE WHEN ... THEN ... END的SQL使用例子

 

SELECT A.ORG_ID, A.SALES_ID, A.SALES_TYPE, A.INV_DATE, A.INVOICE_NO, A.INV_TYPE, A.TAX_KIND,A.PAY_CUSTID, A.MONEY_UNIT, A.PAY_NO,A.AR_PERIOD,A.PL_AR_DATE, A.CLAIM_NO,A.STATUS, A.SALES_NO, A.SALES_DATE, A.SE_ID, A.PO, A.OTYPE, (CASE WHEN (B.PAY_TYPE=1 AND ARDAY_NUM > TO_CHAR(INV_DATE,'DD') ) THEN TO_DATE(TO_CHAR(INV_DATE,'YYYYMM')||LPAD(ARDAY_NUM,2,'0'),'YYYY/MM/DD') ELSE CASE WHEN (B.PAY_TYPE=1 AND ARDAY_NUM<= TO_CHAR(INV_DATE,'DD') ) THEN ADD_MONTHS(TO_DATE(TO_CHAR(INV_DATE,'YYYYMM')||LPAD(ARDAY_NUM,2,'0'),'YYYY/MM/DD'),1) ELSE CASE WHEN (B.PAY_TYPE=2) THEN TRUNC(NEXT_DAY(INV_DATE,ARDAY_NUM+1)) END END END ) AR_DATE, SUM(QTY)QTY, SUM(NET_MONEY) MONEY FROM VW_AR_SALES A, CR_CUST_AR B WHERE A.SALES_TYPE=1 AND A.STATUS =2 AND A.REQ_MARK='Y' AND A.AR_ID IS NULL AND A.ORG_ID=B.ORG_ID AND A.PAY_CUSTID=B.CUST_ID AND B.O_TYPE= A.OTYPE GROUP BY A.ORG_ID, A.SALES_ID, A.SALES_TYPE, A.INV_DATE, A.INVOICE_NO, A.INV_TYPE, A.TAX_KIND,A.PAY_CUSTID, A.MONEY_UNIT, A.PAY_NO,A.AR_PERIOD,A.PL_AR_DATE, A.CLAIM_NO,A.STATUS, A.SALES_NO,A.SALES_DATE, A.SE_ID, A.PO, A.OTYPE,A.UNIT_PRICE,A.MONEY,B.PAY_TYPE,ARDAY_NUM