오라클 캐릭터셋 짜증
http://cafe.naver.com/sqlmvp/303 캐릭터셋때문에 짜증나 죽겄네 ! select distinct(nls_charset_name(charsetid)) CHARACTERSET, decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'), 9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'), 96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'), 112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN from sys.co..
더보기
aaaaaaaa
WITH t(type, name, code) AS ( SELECT '과일', '사과', '0' FROM dual UNION ALL SELECT '과일', '레몬', '1' FROM dual UNION ALL SELECT '과일', '포도', '2' FROM dual UNION ALL SELECT '과일', '참외', '3' FROM dual UNION ALL SELECT '채소', '오이', '0' FROM dual UNION ALL SELECT '채소', '당근', '1' FROM dual UNION ALL SELECT '채소', '호박', '2' FROM dual ) SELECT type , SUBSTR(XMLAgg(XMLElement(x, ',', name) ORDER BY code).Extract..
더보기
시간계산 쿼리
SELECT BASE_DT,PRJT_NM,JOB_ID,SQNC_NO,STRN_DTTM,END_DTTM,EXCT_DTTM,trunc( mod( (END_DTTM-STRN_DTTM)*24, 24 ) )|| --요거슨 HHtrunc( mod( (END_DTTM-STRN_DTTM)*24*60, 60 ) )|| --요거슨 MItrunc( mod( (END_DTTM-STRN_DTTM)*24*60*60, 60 ) ) T --요거슨SS,mod((END_DTTM-STRN_DTTM)*24,60) FROM TDWCMSETLJLOGIWHERE BASE_DT ='20130318' AND JOB_ID like '%TOA%'order by 3
더보기