select *
from prd_rsl as of timestamp to_timestamp(‘20141021140000′,’yyyymmddhh24miss’)
where LOT_NO = ‘S000014900′
;
select *
from prd_rsl as of timestamp(systimestamp-interval ’30’ minute)
where LOT_NO = ‘S000014900’
;
특정시간이나 30분전으로 선택해서 이전의 데이터를 볼 수 있다.
EAI구동에 필요한 설정파일이 관리가 어려운 부분이 있어서 로컬과 ftp서버간에 filesync 기능을 찾던 중 발견한 ftp프로그램
http://winscp.net/eng/docs/lang:ko
사실 ftp는 파일질라 하나면 충분하지만, 이 파일싱크 기능때문에 사용하게 됬다.
WITH PAU AS (
SELECT
PAU_STR_DTM
,PAU_END_DTM
,ROUND(((PAU_END_DTM-PAU_STR_DTM) * 24 * 60), 3) AS DIFF //휴지시간 차이
,CEIL(ROUND(((PAU_END_DTM-PAU_STR_DTM) * 24 * 60), 3)/(7*60)) AS LINE //지나간 근조갯수 구하기
FROM 휴지실적
)
SELECT *
FROM PAU
CONNECT BY LEVEL < LINE
더 좋은 쿼리를 구했다. 분석해서 내껄로 만들자!!
WITH TV_PAU_RSL AS
(
SELECT /*+ MATERIALIZE */
PAU.FAC_TP
, PAU.PROC_TP
, PAU.EQU_CD
, TO_CHAR(PAU.PAU_STR_DTM, 'YYYYMMDDHH24MISS') PAU_STR_DTM
, TO_CHAR(PAU.PAU_END_DTM, 'YYYYMMDDHH24MISS') PAU_END_DTM
, TRUNC(PAU.PAU_STR_DTM, 'MI') AS PAU_STR_DTM2
, TRUNC(PAU.PAU_END_DTM, 'MI') AS PAU_END_DTM2
, TRUNC(PAU.PAU_END_DTM, 'MI') - TRUNC(PAU.PAU_STR_DTM, 'MI') AS CNT
FROM
TB_M30_PAU_RSL PAU
WHERE
PAU.FAC_TP = :FAC_TP
AND PAU.PROC_TP = :PROC_TP
AND TO_CHAR(PAU.PAU_STR_DTM, 'YYYYMMDDHH24MISS') = :PAU_STR_DTM
AND PAU.PAU_END_DTM IS NOT NULL
)
, TV_DT_PAU_RSL_BASE AS
(
SELECT
DISTINCT
CASE WHEN ('060000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS')
AND TO_CHAR(PAU_E_DTM, 'HH24MISS')) THEN DECODE(DUMMY.LVL, 1, PAU.PAU_S_DTM
, TO_DATE(TO_CHAR(PAU.PAU_E_DTM, 'YYYYMMDD') || '060000', 'YYYYMMDDHH24MISS'))
ELSE CASE WHEN ('140000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS')
AND TO_CHAR(PAU_E_DTM, 'HH24MISS')) THEN DECODE(DUMMY.LVL, 1, PAU.PAU_S_DTM
, TO_DATE(TO_CHAR(PAU.PAU_E_DTM, 'YYYYMMDD') || '140000', 'YYYYMMDDHH24MISS'))
ELSE CASE WHEN ('220000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS')
AND TO_CHAR(PAU_E_DTM, 'HH24MISS')) THEN DECODE(DUMMY.LVL, 1, PAU.PAU_S_DTM
, TO_DATE(TO_CHAR(PAU.PAU_E_DTM, 'YYYYMMDD') || '220000', 'YYYYMMDDHH24MISS'))
ELSE PAU_S_DTM END END
END AS PAU_S_DTM
, CASE WHEN '060000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS')
AND TO_CHAR(PAU_E_DTM, 'HH24MISS') THEN DECODE(DUMMY.LVL, 1, TO_DATE(TO_CHAR(PAU.PAU_S_DTM, 'YYYYMMDD') || '060000', 'YYYYMMDDHH24MISS')
, PAU.PAU_E_DTM)
ELSE CASE WHEN '140000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS')
AND TO_CHAR(PAU_E_DTM, 'HH24MISS') THEN DECODE(DUMMY.LVL, 1, TO_DATE(TO_CHAR(PAU.PAU_S_DTM, 'YYYYMMDD') || '140000', 'YYYYMMDDHH24MISS')
, PAU.PAU_E_DTM)
ELSE CASE WHEN '220000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS')
AND TO_CHAR(PAU_E_DTM, 'HH24MISS') THEN DECODE(DUMMY.LVL, 1, TO_DATE(TO_CHAR(PAU.PAU_S_DTM, 'YYYYMMDD') || '220000', 'YYYYMMDDHH24MISS')
, PAU.PAU_E_DTM)
ELSE PAU_E_DTM END END
END AS PAU_E_DTM
FROM (
SELECT
DECODE(CONNECT_BY_ISLEAF, 0, PAU.PAU_STR_DTM2 + ((LEVEL-1)/24)
, PAU.PAU_END_DTM2) AS PAU_S_DTM
, LEAD(DECODE(CONNECT_BY_ISLEAF, 0, PAU.PAU_STR_DTM2 + ((LEVEL-1)/24)
, PAU.PAU_END_DTM2), 1, PAU.PAU_END_DTM2) OVER(ORDER BY LEVEL) AS PAU_E_DTM
FROM
TV_PAU_RSL PAU
CONNECT BY LEVEL <= (PAU_END_DTM2 - PAU_STR_DTM2) * 24 + 2
) PAU
, ( SELECT LEVEL AS LVL FROM DUAL CONNECT BY LEVEL <= 2) DUMMY
ORDER BY PAU_S_DTM
)
, TV_DT_PAU_RSL AS
(
SELECT
CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '000000'
AND '055959' THEN TO_CHAR(PAU_S_DTM-1, 'YYYYMMDD')
ELSE TO_CHAR(PAU_S_DTM, 'YYYYMMDD')
END AS PAU_PSV_DT,
CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '060000'
AND '135959' THEN 1
ELSE CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '140000'
AND '215959' THEN 2
ELSE 3 END
END AS WK_SHF
, ROUND(SUM((PAU_E_DTM - PAU_S_DTM) * 24 * 60),1) AS PAU_HRP
FROM
TV_DT_PAU_RSL_BASE
GROUP BY
CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '000000'
AND '055959' THEN TO_CHAR(PAU_S_DTM-1, 'YYYYMMDD')
ELSE TO_CHAR(PAU_S_DTM, 'YYYYMMDD')
END,
CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '060000'
AND '135959' THEN 1
ELSE CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '140000'
AND '215959' THEN 2
ELSE 3 END
END
)
SELECT
PAU.FAC_TP
, PAU.PROC_TP
, PAU.EQU_CD
, DT_PAU.PAU_PSV_DT
, (
SELECT
NVL(MAX(X.SEQ), 0)
FROM
TB_M30_PAU_RSL_DT X
WHERE
X.FAC_TP = PAU.FAC_TP
AND X.PROC_TP = PAU.PROC_TP
AND X.PAU_PSV_DT = DT_PAU.PAU_PSV_DT
) + (ROW_NUMBER() OVER (ORDER BY 1 DESC)) AS SEQ
, :ObjectType
, :ObjectId
, :ProgramId
, :Timestamp
, :ObjectType
, :ObjectId
, :ProgramId
, :Timestamp
, DT_PAU.PAU_HRP
, TO_DATE(PAU.PAU_STR_DTM, 'YYYYMMDDHH24MISS') AS PAU_STR_DTM
, DT_PAU.WK_SHF
, (SELECT WK_CRW FROM TB_M20_WK_SHF_CRW CRW WHERE CRW.FAC_TP = PAU.FAC_TP AND CRW.WK_PSV_DT = DT_PAU.PAU_PSV_DT AND CRW.WK_SHF = DT_PAU.WK_SHF) AS WK_SHF
FROM
TV_PAU_RSL PAU
, TV_DT_PAU_RSL DT_PAU
WHERE
DT_PAU.PAU_HRP > 0
ORDER BY
DT_PAU.PAU_PSV_DT
SELECT TABLE_NAME, MAX(COMMENTS) AS TAB_COMMENTS, WM_CONCAT(COLUMN_NAME)
FROM (
SELECT A.TABLE_NAME, C.COMMENTS, A.COLUMN_NAME||'(‘||TRIM(B.COMMENTS)||’)’ AS COLUMN_NAME
FROM ALL_IND_COLUMNS A, ALL_COL_COMMENTS B, ALL_TAB_COMMENTS C
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND A.TABLE_NAME = C.TABLE_NAME
AND A.TABLE_NAME LIKE ‘TB_%’
)
GROUP BY TABLE_NAME