테이블 TSYCO_BATCH :
PGM_ID(varchar2)
PGM_NM(varchar2)
PGM_REQ(varchar2)
BATCH_TYPE(varchar2)
BASIC_DATE
REGR_ID
REG_DATE(date)
CHGR_ID
CHG_DATE(date)
가데이터 예시>
BATCH001 주배치 10000001 weekly 목 P029411 2012/09/05 00:00:02 P029412 2012/10/01 10:10:00
테이블 TSYCO_BATCH_LOG :
BATCH_SEQ
PGM_ID
STRT_DTTM
END_DTTM
SUCS_CNT
FAIL_CNT
REGR_ID
REG_DATE
CHGR_ID
CHG_DATE
가데이터 예시>
0000001 BATCH001 20121004000000 2012100400000 1 3 P029411 2012/09/05 00:00:02 P029412 2012/10/01 10:10:00
*---조건은 BATCH_TYPE(monthly,weekly,daily)에 따라 배치가 실행되고 로그가 싸이면 조회가 됩니다.
배치실행이 안되면 로그에 안남으니깐..
가짜데이타를 만들어서 날짜조건(기간)에 맞게 뿌려줘야 한다네요..
설명이 허접하지만 부탁드립니다.---*
(참고쿼리)
SELECT BAT.PGM_ID
, BAT.PGM_REQ
, BAT.BATCH_TYPE
, BAT.YMD
, BAT.PGM_NM
, LOG.BATCH_SEQ
, LOG.PGM_ID
, LOG.STRT_DTTM
, LOG.END_DTTM
, LOG.SUCS_CNT
, LOG.FAIL_CNT
, LOG.PEGR_ID
, LOG.REG_DATE
, LOG.REG_DATE
, LOG.CHGR_ID
, LOG.CHG_DATE
, DECODE(LOG.PGM_ID,NULL,'미처리','처리') RESULT
FROM (SELECT PGM_ID, PGM_NM, PGM_REG, BATCH_TYPE, BASIC_DATE,DD,DY,YMD
FROM TSYCO_BATCH A
<![CDATA[
,(SELECT SUBSTR(TO_CHAR(TO_DATE(#SEARCH_START_DT#,'YYYYMMDD') + LEVEL -1,'YYYYMMDD'),7,2) AS DD
,TO_CHAR(TO_DATE(#SEARCH_START_DT#,'YYYYMMDD') + LEVEL -1, 'DY') DY
,TO_CHAR(TO_DATE(#SEARCH_START_DT#,'YYYYMMDD') + LEVEL -1, 'YYYYMMDD') YMD
FROM DUAL
CONNECT BY TO_DATE(#SEARCH_START_DT#,'YYYYMMDD') + LEVEL -1 <= TO_DATE(#SEARCH_END_DT#,'YYYYMMDD')) B
]]>
WHERE 1=1
AND BASIC_DATE = DECODE (BATCH_TYPE, 'weekly', DY, 'monthly', DD, '%')
) BAT
, (SELECT BATCH_SEQ
, PGM_ID
, SUBSTR(STRT_DTTM,1,8) AS YMD
, STRT_DTTM
, END_DTTM
, SUCS_CNT
, FAIL_CNT
, REGR_ID
, REG_DATE
, CHGR_ID
, CHG_DATE
FROM TSYCO_BATCH_LOG) LOG
WHERE 1=1
AND BAT.PGM_ID = LOG.PGM_ID(+)
AND BAT.YMD = LOG.YMD(+)
ORDER BY BAT.PGM_ID, BAT.YMD, LOG.BATCH_SEQ