JOIN QUERY,TO_CHAR(SYSDATE,'YYYYMMDD')

1. JOIN QUERY - EXAMPLE

-- Data Requirements:
-- Data Base : All Client until 31 December 2008
-- Cash + Portfolio Value as of July 9th (yesterday)
-- Last Transaction Date based on last Done Transaction date

SELECT
 A10.ACNT_NO
 , A10.ACNT_NM
 , A10.ESTBL_DATE AS JOINT_DATE
 , A16.INV_DEP_CASH AS CASH_VALUE
 , A16.LAST_TR_DATE AS LAST_TR_DATE
 , T5.TOTAL AS STOCK_VALUE
 , A20.CNTC_NO AS CONTACT
 , A10.INV_ACC_NO
 ,CASE A10.INV_BANK_CODE
 WHEN '88' THEN 'SHINHAN'
 WHEN '99' THEN 'KB'
 ELSE ''
 END AS BANK_NAME
 , A10.SID
 ,A10.KSEI_ACNT_NO
FROM A100M A10, A106H A16, A200M A20, (SELECT ACNT_NO, SUM(BLQTY*CLPR) AS TOTAL FROM T500H WHERE TR_DATE= TO_CHAR(SYSDATE-1, 'YYYYMMDD') GROUP BY ACNT_NO) T5
WHERE A10.INV_ACC_NO IS NOT NULL --BANK
AND A10.INV_STA_YN ='Y' -- BANK
AND A10.ESTBL_DATE <= '20081231'
AND A16.TR_DATE= TO_CHAR(SYSDATE-1, 'YYYYMMDD')
AND A10.ACNT_NO = A16.ACNT_NO
AND A16.ACNT_NO = T5.ACNT_NO (+)
AND A16.ACNT_NO = A20.ACNT_NO (+)
ORDER BY A10.ACNT_NO;

2. 날짜 관련(TO_CHAR(SYSDATE,'YYYYMMDD')) - 아래 1)과 2) 쿼리는 동일하다.

1) SELECT * FROM EMP WHERE HIREDATE=TO_CHAR(SYSDATE,'YYYYMMDD');

2) SELECT * FROM EMP WHERE HIREDATE = '20120716';