두시간의 차이를 구한다.
디비에 저장되어 있는 두개의 시간읠 비교하여 분단위로 조회한다. SELECT TO_NUMBER(SUBSTR(DIFF, 2, 9)) DD , TO_NUMBER(SUBSTR(DIFF, 12, 2)) HH , TO_NUMBER(SUBSTR(DIFF, 15, 2)) MM , TO_NUMBER(SUBSTR(DIFF, 18, 2)) SS , DECODE( TO_NUMBER(SUBSTR(DIFF, 12, 2)), 0, SUBSTR(DIFF, 15, 2), (TO_NUMBER(SUBSTR(DIFF, 12, 2)) * 60) + TO_NUMBER(SUBSTR(DIFF, 15, 2))) AS HHplusMM --시간 분을 분으로만 표현 FROM ( SELECT NUMTODSINTERVAL (DATE2 - DATE1, 'D..
2016. 5. 16.
특정구분자로 구성된 문자열을 row로 변환하기(split) 1
데이터베이스에 다음과 같은 키('111,222,333,444,555')를 던져주고 row로 변환하여 다른 테이블과 join, insert 등의 작업을 할 때 사용한다. SELECT REGEXP_SUBSTR('111,222,333,444,555','[^,]+', 1, LEVEL) FROM DUAL CONNECT BY INSTR('111,222,333,444,555', ',', 1, LEVEL - 1) > 0 응용1다른 테이블의 테이터와 join 하여 insert를 할 때 INSERT INTO TB_ITEM_FILE ( ITEM_CD, FILE_ID, ) SELECT P_ITEM_CD, A.FILE_ID, FROM TB_COM_FILE_INFO A , ( SELECT REGEXP_SUBSTR('111,222..
2016. 1. 8.
output변수 로 테이블 복수개 조회
procedure GetMethod(ProjectCode in varchar2 ,out_Cursor1 out sys_refcursor ,out_Cursor2 out sys_refcursor) is begin open out_Cursor1 for select AA ,BB ,CC ,DD ,EE ,FF from t_table1 where Proj_Cd = ProjectCode order by Evlt_Ordr; open out_Cursor2 for select AA ,CC ,DD ,GG ,HH from t_table2 where Proj_Cd = ProjectCode; end;
2015. 12. 4.
[Sample] Left outer join 비교
SELECT c.REQ , a.CD , a.NM , b.VAL , a.TYP , a.DIV FROM TB_ATTB_MST a LEFT OUTER JOIN TB_ATTB b ON a.CD = b.ID AND b.NO = '1321313810199' LEFT OUTER JOIN TB_MST c ON b.NO = c.NO AND c.NO = '1321313810199' where TYP = 02' ; SELECT b.REQ , a.CD , a.NM , b.VAL , a.TYP , a.DIV FROM TB_ATTB_MST a ,TB_ATTB b WHERE a.CD = b.ID(+) AND a.TYP = 02' AND b.REQ(+) = '1321313810199'
2015. 7. 31.
[Sample] Pivot, multi pivot
SELECT REQ_NO , CENT_CD , SPCM_COL_DM , BLD_NM , ZIP_CD , ADDR , ADDR_DTL FROM ( ) PIVOT( MAX(ATTB_VAL) FOR ATTB_ID IN ( 'CENT_CD' AS HEL_CENT_CD , 'SPCM_COL_DM' AS SPCM_COL_DM , 'BLD_NM' AS BLD_EQMT_NM , 'ZIP_CD' AS PAT_ZIP_CD , 'ADDR' AS PAT_ADDR , 'ADDR_DTL' AS PAT_ADDR_DTL ) ) select * from t1 pivot xml ( sum(c3) for c2 in (any) ) ; select * from t1 pivot xml ( sum(c3) for c2 in (select dist..
2015. 7. 31.
[Query] Insert with
INSERT WITH INSERT INTO TB_ACC_OUT_CLS(CLS_DT, OUT_CST_CD, SC_CD, CST_CD, ITEM_CD ) WITH VI_CLS AS( SELECT A.REQ_DT , A.OUT_CD , B.SB_CD , B.CST_CD , A.ITEM_CD , SUM(1) REQ_CNT , SUM(NVL(A.TEST_AMT,0)) REQ_AMT , SUM(NVL(A.STU_AMT,0)) OUT_REQ_AMT FROM TB_DTL A , TB_MST B , TB_ITEM E , TB_OUT F WHERE A.REQ_NO = B.REQ_NO AND A.ITEM_CD = E.ITEM_CD AND E.STR_DT = TO_CHAR(SYSDATE,'YYYYMMDD') GROUP BY ..
2015. 7. 31.
