빵찌니 개발 노트

오라클 오류 중에 ORA-01858 숫자가 있어야 하는 위치에서 숫자가 아닌 문자가 발견되었습니다. 

(ORA_01858 - a non-numeric character was found where a numeric was expected)

에러 해결 방법

원인 : TO_DATE는 숫자타입을 날짜타입으로 변환시켜주는 형변환 함수이며 컬럼명에 숫자값이 들어가야하는데 

         문자값이 들어가서 발생한 에러

해결 : 문자형태의 숫자값을 적으면 됨

select to_date('20/08/11 13:20','YYYYMMDDHH24MISS') from dual;  --> X 

select to_date(20210803, 'YYYYMMDD') from daul;  --> O

차암쉽죠~

@@@ 기본정보보기
DESCRIBE TABLE table-name [show detail]
DESCRIBE SELECT * FROM tablename;

@@@ DB2 인덱스 정보보기
DESCRIBE INDEXES FOR TABLE table-name [show detail]

@@@ 등록 테이블 리스트 보기
LIST TABLES FOR ALL ;

@@@ LOCK 상태 확인
GET SNAPSHOT FOR LOCKS ON depsdb
유지되는 잠금, 현재 잠금대기중인 에이전트, 응용프로그램명,
응용프로그램 상태, 총대기시간, 모드상태 등을 확인

@@@ Oracle의 top 5 와 같은 원하는 건수만 나오게
FETCH FIRST 5 ROWS ONLY
라고 하면 하면 처음 5개의 row가 나옵니다.

@@@ INTEGER형으로 변환
ex)
SELECT INTEGER(zip_code)
FROM zipcode
FETCH FIRST 5 ROWS ONLY


@@@ DOUBLE형으로 변환
ex)
SELECT DOUBLE(zip_code)
FROM zipcode
FETCH FIRST 5 ROWS ONLY


@@@ SUBSTR (SUBSTRING)
ex)
SELECT SUBSTR(zip_code,1,3)
FROM zipcode
FETCH FIRST 5 ROWS ONLY


@@@ CHAR
ex)
SELECT CHAR(doseo)
FROM zipcode
FETCH FIRST 5 ROWS ONLY


@@@ COALESCE - Oracle Nvl()기능
컬럼 타입에 따라 인수를 결정한다. COALESCE(문자형,문자형표시) COALESCE(숫자형,숫자형표시)
ex)
SELECT COALESCE(doseo,'1')
FROM zipcode
FETCH FIRST 5 ROWS ONLY


@@@ ||
문자연결기능
SELECT COALESCE(doseo,'1') || zip_code
FROM zipcode
FETCH FIRST 5 ROWS ONLY


@@@ page기능
SELECT t.zip_code,page
FROM (
SELECT zip_code,((ROWNUMBER() OVER() -1)/ 10+1) AS page
FROM zipcode
) AS t
WHERE t.page = 3
FETCH FIRST 100 ROWS ONLY
WITH UR


@@@ year 구하기
ex1)
SELECT YEAR(CURRENT TIMESTAMP)
FROM SYSIBM.SYSDUMMY1
WITH UR

ex2)
SELECT YEAR('2004-05-16')
FROM SYSIBM.SYSDUMMY1
WITH UR

ex3)
SELECT YEAR(CURRENT DATE)
FROM SYSIBM.SYSDUMMY1
WITH UR


@@@ month 구하기
ex1)
SELECT MONTH(CURRENT TIMESTAMP)
FROM SYSIBM.SYSDUMMY1
WITH UR

ex2)
SELECT MONTH('2004-08-16')
FROM SYSIBM.SYSDUMMY1
WITH UR


@@@ day 구하기
ex1)
SELECT DAY(CURRENT TIMESTAMP)
FROM SYSIBM.SYSDUMMY1
WITH UR

EX2)
SELECT DAY('2004-08-16')
FROM SYSIBM.SYSDUMMY1
WITH UR





@@@ week 구하기
ex)
SELECT WEEK('2004-05-16')
FROM SYSIBM.SYSDUMMY1
WITH UR

@@@ time 구하기

ex)
SELECT CURRENT TIME
FROM SYSIBM.SYSDUMMY1
WITH UR


@@@ dayofyear 구하기(오늘이 365일중 몇번째 날짜)
ex)
SELECT DAYOFYEAR(CURRENT DATE)
FROM SYSIBM.SYSDUMMY1
WITH UR


@@@ dayname 구하기(요일 이름)
ex)
SELECT DAYNAME(CURRENT DATE)
FROM SYSIBM.SYSDUMMY1
WITH UR


@@@ CONCAT 문자연결함수
ex)
SELECT CONCAT('111','22222 ')
FROM SYSIBM.SYSDUMMY1
WITH UR


@@@ MOD 나머지 함수
ex)
SELECT MOD(11111,100)
FROM SYSIBM.SYSDUMMY1
WITH UR


@@@ value 함수 - COALESCE와 동일한 기능
ex)
SELECT VALUE(CURRENT DATE,'2004-08-16')
FROM SYSIBM.SYSDUMMY1
WITH UR


@@@ abs 함수 절대값 함수
ex)
SELECT ABS(-51234)
FROM SYSIBM.SYSDUMMY1
WITH UR


@@@ lcas 함수 대문자를 소문자로
ex)
SELECT LCASE('ABCD')
FROM SYSIBM.SYSDUMMY1
WITH UR;


@@@ ucase 함수 소문자를 대문자로
ex)
SELECT LCASE('abcd')
FROM SYSIBM.SYSDUMMY1
WITH UR;


@@@ multiply_alt 두 수를 곱한다.
ex)
SELECT MULTIPLY_ALT(3,20)
FROM SYSIBM.SYSDUMMY1
WITH UR;


@@@ round
ex)
SELECT ROUND(873.726, 2), ROUND(873.726, 1), ROUND(873.726, 0), ROUND(873.726,-1),

ROUND(873.726,-2), ROUND(873.726,-3), ROUND(873.726,-4)
FROM SYSIBM.SYSDUMMY1
WITH UR;


@@@ week_iso 함수
ex)
SELECT WEEK_ISO(CURRENT DATE) SELECT WEEK_ISO('1997-12-28')
FROM SYSIBM.SYSDUMMY1 FROM SYSIBM.SYSDUMMY1
WITH UR; WITH UR;


@@@ dayofweek_iso 해당주에서 몇일에 해당하는지
ex)
SELECT DAYOFWEEK_ISO(CURRENT DATE) SELECT DAYOFWEEK_ISO('2004-08-16')
FROM SYSIBM.SYSDUMMY1 FROM SYSIBM.SYSDUMMY1
WITH UR; WITH UR;




도움되셨다면 공감 부탁드려요~~^^

같은 테이블에 값이 있으면 UPDATE하고 없으면 INSERT 해야 할 때 한방 쿼리로 만들기!

또는

대상 테이블과 다른 테이블을 조인해 값을 비교해야 할 경우도 MERGE INTO로 UPDATE/ INSERT 하기.

1. 같은 테이블에 값이 있으면 UPDATE, 없으면 INSERT

대상 테이블 T1

MERGE INTO T  -- 대상테이블

USING DUAL --동일 테이블이라면 daul

ON (NAME = '홍길동' -- 조건1

AND ID = 'TestID' )    -- 조건2

WHEN MATCHED THEN  --있으면 update

UPDATE SET

컬럼1 = 값1,

컬럼2 = 값2

DELETE WHERE (조건)  -- ** DELETE 문 포함가능

WHEN NOT MATCHED THEN  --없으면 insert

INSERT(NAME, ID, 컬럼1, 컬럼2)

;

2. 다른 테이블과 조인해 값을 비교해야할 경우

MERGE INTO INFO AS T1

USING (

SELECT

A.SEQ

, B.GRD

, B.STAT

FROM INFO A, TARGET B

WHERE A.SEQ = B.SEQ

) T2

ON ( T1.SEQ = T2.SEQ

AND T1.ID = #{char1}

)

WHEN MATCHED THEN

UPDATE

SET T1.GRD = #{grd}

, T1.UPT_DT = SYSDATE

, T1.UPT_ID = #{userID}

WHEN NOT MATCHED THEN

INSERT

(

T1.SEQ, A.ID, T1.GRD, T1.RGIST_DT, T1.RGIST_ID, T1.UPT_DT, A.UPD_ID

)

VALUES

(

#{char1}, #{char2}, #{char3}, #{char4}, #{char5}, #{char6}, #{char7}

)

;

MERGE는 오라클 9버전 이상 가능하니 참고하세요~

1. SELECT SYSDATE FROM DAUL

- > 2018/02/22 12:30:00

2. SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') SYS_DATE24 FROM DUAL

-> 20180222133000

DATE 타입을 CHAR 타입으로 변경

3. SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH12MISS') SYS_DATE12 FROM DAUL

- > 20180222013000

4. SELECT TO_DATE('20180222013000', 'YYYYMMDDHH24MISS') SYS_DATE FROM DAUL

- > 2018/02/22 13:30:00

5. SELECT TO_CHAR(SYSDATE, 'YYYY') SYS_YEAR4 FROM DUAL

- > 2018

6. SELECT TO_CHAR(SYSDATE, 'YY') SYS_YEAR2 FROM DUAL

- > 18

- > 02

1. Decode란?

• Oracle에서 사용하는 조건문으로 Decode를 사용하여 조건에 따라 다른 결과값을 표기할 수 있다.
• 표현식 -> DECODE(대상값, 조건1, 결과1, 조건2, 결과2 ....)
• JAVA에서의 IF문을 중첩으로 사용하는 것 처럼 DECODE도 중첩으로 사용할 수 있다.

DECODE 사용방법을 소개하겠습니다. Oracle에서 조건문 같은 함수로 조건절에 부합하는 항목에 대해서 결과값을 정해서 뽑을 수 있는 장점을 가지고 있습니다.

어찌보면 IF문과 비슷해 보이지만 실상 SWITCH CASE문 하고 많이 닮았습니다.

2. 예제

2.1) 직급에 조건을 주어 한글 명칭으로 변경하기

다음은 EMP 테이블에서 직원 번호, 이름 등을 추출하고 JOB 항목에 대해서 DECODE를 걸었습니다.

JOB의 값이 CLERK면 직원으로 SALESMAN이면 영업으로 표시하고 나머지 항목은 ?로 처리하라는 SQL문입니다. 

2.3) 직급에 명칭에 따라 연봉을 함수로 나타내기

다음은 직급을 GROUP BY 로 묶고 직급이 PRESIDENT에 대한 연봉의 최대값을 MANAGER 일 경우에는 그 직급에 대한 최소 연봉을 표기하고 나머지는 비밀로 표기합니다. 

GROUP BY 로 컬럼을 묶고 DECODE를 사용할때 주의점은 조건대상과 결과값은 타입은 항상 같아야 합니다.

위의 예제에서는 결과를 MAX(SAL)로 표기하면 NUMBER 타입이 되기 때문에 VARCHAR2인 JOB는 맞지 않아 에러가 나게 됩니다.

그래서 TO_CHAR를 써서 MAX에 대한 결과를 문자 형태로 바꿔주어서 결과를 받을 수 있었습니다.