번호 생성 질문드립니다. 0 8 808

by xcrew [SQL Query] [2018.03.08 13:36:09]


안녕하세요.

번호(RECEIVE_NO)를 생성하여 만들어주고 있습니다.

RECEIVE_NO는 아래와같이 들어가 있습니다.

ENG2017024, ENG2017026,........ENG2017094

번호 생성 쿼리는 아래와 같습니다.

SELECT 
'2017'||NVL(LPAD(MAX(SUBSTR(RECEIVE_NO,8,3))+1,3,'0'),'001') RECEIVE_NO
FROM CE_JINDAMYONGYEOK
WHERE SUBSTR(RECEIVE_NO,4,4) = '2017'

이런식으로 만든다음 나중에 'ENG'를 붙여주는데요.....

그런데 

SELECT
 MAX(SUBSTR(RECEIVE_NO,8,3)) FROM CE_JINDAMYONGYEOK

WHERE SUBSTR(RECEIVE_NO,4,4) = '2017'

이렇게만 했을때 값이 999가 나와서

RECEIVE_NO CHAR(10 BYTE) 에서 RECEIVE_NO CHAR(11BYTE)로 변경한 다음

SELECT 
'2017'||NVL(LPAD(MAX(SUBSTR(RECEIVE_NO,8,4)+1,4'0'),'001') RECEIVE_NO
FROM CE_JINDAMYONGYEOK
WHERE SUBSTR(RECEIVE_NO,4,4) = '2017'

4로 변경을 하였습니다.

20171000이 나오는데요 테스트를 위해서 '20171000'을 디비에 넣어놓고 돌려봐도

20171000이 나옵니다....20171001이 나와야 되는거 아닌가요??

저런 방식이 괜찮은건지........

도움 부탁드립니다.

 

 

by 마농 [2018.03.08 13:58:29]

1. 원인은?
  - 문자열 비교시 '1000' 보다 '9' 가 큽니다.
2. 자리수를 늘렸다면?
  - 기존 값들의 자리수도 모두 늘려주셔야 합니다.
3. 문제가 많습니다.
  - 컬럼 가공으로 인덱스를 탈 수 없으므로 비효율입니다.
  -  인덱스 읽어 1건만 읽고 멈추는 효율적인 쿼리로 바꿔야 합니다.
  - 그러려면 위 2번 일괄 갱신 작업 선행되어야 합니다.
  - 그런데 키를 바꾸었을 때 이 키를 참조하는 다른 테이블도 모두 바꿔줘야 하는 문제점이 있네요.
  - 이 키를 전체 일괄 갱신 가능한지? 고민해 보세요.


by 신이만든지기 [2018.03.08 14:00:06]
with CE_JINDAMYONGYEOK(RECEIVE_NO) as (
select 'ENG2017024' from dual union all
select 'ENG2017025' from dual union all 
select 'ENG2017026' from dual) 
SELECT '2017' || NVL (LPAD (to_number(MAX (SUBSTR (RECEIVE_NO, 8))) + 1, 4, '0'), '0001') RECEIVE_NO 
  FROM CE_JINDAMYONGYEOK
 WHERE SUBSTR (RECEIVE_NO, 4, 4) = '2017'

 

원하시는 형태는 이 쿼리로 해보시기 바랍니다. 

가급적 시퀀스를 만들어 사용하는 것이 더 좋을 것 같습니다.

그리고 기존 데이터도 전부 4자리로 업데이트 하는 것을 잊지 마시기 바랍니다.

ex) UPDATE CE_JINDAMYONGYEOK
   SET RECEIVE_NO = SUBSTR(RECEIVE_NO, 1,7) || '0' || SUBSTR(RECEIVE_NO, 8)
  FROM CE_JINDAMYONGYEOK
 WHERE LENGTH(RECEIVE_NO) = 10;

 


by xcrew [2018.03.08 14:59:40]

감사합니다~!

 


by 마농 [2018.03.08 14:04:24]
-- 일괄 갱신 --
UPDATE ce_jindamyongyeok
   SET receive_no = SUBSTR(receive_no, 1, 7) || LPAD(SUBSTR(receive_no, 8), 4, '0')
 WHERE LENGTH(receive_no) = 10
;
-- 일괄 갱신 후 가장 효율적인 쿼리 --
SELECT 'ENG' || '2017' || LPAD(NVL(SUBSTR(MAX(receive_no), 8) + 1, 1), 4, '0') receive_no
  FROM ce_jindamyongyeok
 WHERE receive_no LIKE 'ENG' || '2017' || '%'
;
-- 일괄 갱신 불가능 할 경우 그나마 대안 쿼리 --
SELECT 'ENG' || '2017' || LPAD(NVL(MAX(SUBSTR(receive_no, 8) + 1), 1), 4, '0') receive_no
  FROM ce_jindamyongyeok
 WHERE receive_no LIKE 'ENG' || '2017' || '%'
;

 


by xcrew [2018.03.08 14:59:53]

항상 감사드립니다~!


by xcrew [2018.03.08 16:15:49]

마농님 일괄갱신을 하려고 컬럼길이를 10에서 11로 늘리고 일괄갱신을 하려니

WHERE LENGHT(receive_no) = 10인게 조회가 안되고

WHERE LENGHT(receive_no) = 11로 해야 나옵니다. ㅠㅠ

열에 대한 값이 너무 큼이라고 나오는데요......조언 부탁드립니다...


by 우리집아찌 [2018.03.08 15:51:54]

인조실별자(인조키) 경우는 가급적 숫자 TYPE으로 하세요.


by 마농 [2018.03.08 16:32:21]
-- VARCHAR2 가 아니라 CHAR 군요. --
UPDATE ce_jindamyongyeok
   SET receive_no = SUBSTR(receive_no, 1, 7) || '0' || SUBSTR(receive_no, 8, 3)
 WHERE LENGTH(TRIM(receive_no)) = 10
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입