ORDER BY nulls FIRST, case when 등등 가능
WITH TEMP AS ( SELECT 'DDDD' AS COD, '1111' AS NAM, NULL AS DPT FROM DUAL UNION ALL SELECT 'AAAA' AS COD, 'AAAA' AS NAM, '600' AS DPT FROM DUAL UNION ALL SELECT 'CCCC' AS COD, 'XXXX' AS NAM, '300' AS DPT FROM DUAL UNION ALL SELECT 'AAAA' AS COD, '에이이에' AS NAM, '400' AS DPT FROM DUAL UNION ALL SELECT 'CCCC' AS COD, '일일일' AS NAM, '900' AS DPT FROM DUAL UNION ALL SELECT 'BBBB' AS COD, 'TWOTWO' AS N..
DBMS_RANDOM 렌덤값처리
셈플형식을 참고해서, 10000줄짜리 임의 데이터 생성쿼리를 작성 셈플형식 SELECT '123963' AS 사번 --//6자리숫자 고정 , 'Asdfasdf' AS 성 --//첫글자대문자 , 'Hewerw' AS 이름 --//첫글자대문자 , 'AR001' AS 관리유형 --//5글자문자 고정 , '20160801' AS 생일 --//8자리날짜범위내의 문자 FROM DUAL; - 사용목적 테이블 작업후, 데이터가 없어, 업무분석이 어려울때, 임의로 데이터를 생성해서, 추가후에 임시로 작업을 진행할수 있다. SELECT LPAD(ROWNUM,6,'0') AS 사번 --//6자리숫자 고정 , INITCAP(DBMS_RANDOM.STRING('A', 6)) AS 성 --//첫글자대문자 , INITCAP(DBM..
Oracle ROW_NUMBER, MIN, MAX, SUM, FIRST_VALUE, LAST_VALUE, LAG, LEAD
WITH TB AS ( SELECT 'DDDD' AS COD, 'AAAA' AS NAM, NULL AS DPT FROM DUAL UNION ALL SELECT 'AAAA' AS COD, 'BBBB' AS NAM, '200' AS DPT FROM DUAL UNION ALL SELECT 'AAAA' AS COD, 'CCCC' AS NAM, '300' AS DPT FROM DUAL UNION ALL SELECT 'BBBB' AS COD, 'DDDD' AS NAM, '100' AS DPT FROM DUAL UNION ALL SELECT 'AAAA' AS COD, 'EEEE' AS NAM, '600' AS DPT FROM DUAL UNION ALL SELECT 'CCCC' AS COD, 'FFFF' AS NAM,..
Oracle NVL, NVL2, DECODE, NULLIF, COALESCE 함수
WITH TA AS ( SELECT '11' AS CHK1 , '12' AS CHK2 , '13' AS CHK3 , '14' AS CHK4 FROM DUAL UNION ALL SELECT '21' AS CHK1 , '22' AS CHK2 , '23' AS CHK3 , '24' AS CHK4 FROM DUAL UNION ALL SELECT '' AS CHK1 , '32' AS CHK2 , '33' AS CHK3 , '34' AS CHK4 FROM DUAL UNION ALL SELECT '' AS CHK1 , '' AS CHK2 , '43' AS CHK3 , '44' AS CHK4 FROM DUAL UNION ALL SELECT '' AS CHK1 , '' AS CHK2 , '' AS CHK3 , '54' ..
