본문 바로가기

IT/DB

[Oracle] ORDER BY에서 NULL 위치 [Oracle] ORDER BY에서 NULL 위치 ORDER BY [COL] [DESC] NULLS FIRST => NULL 값을 먼저 나오도록 함. ORDER BY [COL] [DESC] NULLS LAST => NULL 값을 뒤에 나오도록 함. 더보기
[Oracle] SPLIT -- ORACLE SPLIT QUERY -- 01. 문자 SELECT CODE_ARR FROM ( SELECT SUBSTRB( :AS_LIST , INSTRB(','||:AS_LIST, ',', 1, ROWNUM) , INSTRB(:AS_LIST||',', ',', LEVEL) - INSTRB(','||:AS_LIST, ',', 1, ROWNUM) ) AS CODE_ARR FROM DUAL WHERE 1=1 AND REPLACE(:AS_LIST,',','') IS NOT NULL AND INSTRB(:AS_LIST||',', ',', LEVEL) = LEVEL CONNECT BY LEVEL 더보기
[Oracle] CLOB 타입 REPLACE 시키기 #1. REPLACE 1) TO_CHAR( [CLOB] ) 2) REPLACE( TO_CHAR( [CLOB] ), STR1, STR2 ) 3) TO_CLOB( REPLACE( TO_CHAR( [CLOB] ), STR1, STR2 ) ) 단, [CLOB]이 4000이 넘으면.. 안된다... OTL... #2. REGEXP_REPLACE (CLOB 타입 처리 가능) 1) REGEXP_REPLACE( [CLOB], STR1, STR2 ) ※ 정규식으로 해결할 수 있다. 더보기
[Oracle] MODEL SELECT YM, COL_AMT FROM ( SELECT '201401' AS YM, 10000 AS AMT FROM DUAL UNION ALL SELECT '201402' AS YM, 20000 AS AMT FROM DUAL UNION ALL SELECT '201403' AS YM, 30000 AS AMT FROM DUAL UNION ALL SELECT '201404' AS YM, 40000 AS AMT FROM DUAL UNION ALL SELECT '201405' AS YM, 50000 AS AMT FROM DUAL ) WHERE 1=1 MODEL DIMENSION BY (YM) MEASURES (AMT, COL_AMT) RULES ( COL_AMT['SUM'] = SUM(COL_AMT) [YM .. 더보기
[ORACLE] 통계 방법 1. COPYT A. LAY_OUT B. DATA -> GROUP BY SUM C. COPYT (SEQ 1-3) ( A - B ) - C [JOIN] DECODE SUM 2. FIRST_VALUE, NTH_VALUE A. LAY_OUT + UNION ALL 2 (SUM, 97%) B. GROUPING SETS ( (a, b, c), () ) SUM + UNION ALL 1 (97%) + FIRST_VALUE(d) OVER (ORDER BY a DESC) AS CALC OR NTH_VALUE(d, 1) OVER (ORDER BY a DESC) AS CALC A - B [JOIN] DECODE SUM 개인적인 생각으론, 1)을 많이 사용하였는데... COPYT 개수만큼 데이터 ROW가 생성되니... 2) 방.. 더보기

티스토리툴바