DB2 NULL값을 0으로 변환후 MAX + 1 구하는 법

1. 실제 사용 테스트 쿼리

SELECT MAX(SEQ) AS MAXSEQ,

COALESCE(MAX(SEQ), ‘0’) + 1 AS NEXTSEQ,

CASE WHEN MAX(SEQ) IS NULL THEN 0 ELSE MAX(SEQ) +1 AS NEXTSEQ2

FROM TB_CERT_HISTORY

WHERE DOC_NO = ‘T-001’

 

여기서 SEQ의  MAX값을 구하려면 DOC_NO로 GROUP BY를 먹여야 되지 않나 하는 실수를 하지말자…삽질을 너무 오래했다…

 

2. 참고 URL : http://blog.naver.com/PostView.nhn?blogId=qmffjem09&logNo=70036028605&categoryNo=17&viewDate=&currentPage=1&listtype=0

 

3. 블로그 내용

//MSSQL

select ISNULL(max(WR_NO),0) as u_id from vote_Doc

//DB2
EX1)
select CASE WHEN MAX(WR_NO) IS NULL
THEN 0
ELSE MAX(WR_NO) END as u_id
from vote_Doc
EX2)
select COALESCE(MAX(WR_NO),0) as u_id
from vote_Doc

값증가시
만약 MAX(u_id)에 1을 증가하려면

select CASE WHEN MAX(WR_NO) IS NULL
THEN 0
ELSE MAX(WR_NO) + 1 END as u_id
from vote_Doc

== 동일표현식 하나 더

select COALESCE(MAX(WR_NO) + 1,0) as u_id
from vote_Doc

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s