테이블의 컬럼 중 발행금액, 입금액 등을 계산해서 미수금 컬럼값 sql문장으로 자동 넣어주기 (COALESCE)

컬럼이 만들다보니 160개가 넘어가는 공사관련 테이블인데, 화면에 미수금을 보여주려고 하니. 좀 복잡하네요.

실행결과

아래의 코드로 각 varchar(15)로 선언된 컬럼의 값을 계산하는 sql 명령어인데,

도움이 될 것 같아 올려봅니다.

UPDATE work
SET sum_receivable = (
    COALESCE(CAST(REPLACE(bill1, ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(bill2, ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(bill3, ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(bill4, ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(bill5, ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(bill6, ',', '') AS DECIMAL(15, 2)), 0)
) - (
    COALESCE(CAST(REPLACE(deposit1, ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(deposit2, ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(deposit3, ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(deposit4, ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(deposit5, ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(deposit6, ',', '') AS DECIMAL(15, 2)), 0)
);


위의 쿼리로 하니 경고가 나옵니다.

위의 코드를 실행하니, 아래의 경고가 나오지만, 계산은 된것 같은데 맞는가?

Warning: #1292 Truncated incorrect DECIMAL value: ''

UPDATE work
SET sum_receivable = (
    COALESCE(CAST(REPLACE(NULLIF(bill1, ''), ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(NULLIF(bill2, ''), ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(NULLIF(bill3, ''), ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(NULLIF(bill4, ''), ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(NULLIF(bill5, ''), ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(NULLIF(bill6, ''), ',', '') AS DECIMAL(15, 2)), 0)
) - (
    COALESCE(CAST(REPLACE(NULLIF(deposit1, ''), ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(NULLIF(deposit2, ''), ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(NULLIF(deposit3, ''), ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(NULLIF(deposit4, ''), ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(NULLIF(deposit5, ''), ',', '') AS DECIMAL(15, 2)), 0) + 
    COALESCE(CAST(REPLACE(NULLIF(deposit6, ''), ',', '') AS DECIMAL(15, 2)), 0)
);

 

경고메시지가 나와서 다시 실행했는데, 위의 결과 이미지에서 보듯이 깨끗하게 잘되네요.

이 경고 메시지, Warning: #1292 Truncated incorrect DECIMAL value: '',는 SQL이 빈 문자열('')을 DECIMAL 형으로 변환하려 할 때 발생합니다. 빈 문자열은 DECIMAL 형으로 직접 변환할 수 없기 때문에 이 경고가 발생합니다. 하지만 COALESCE 함수로 인해, 빈 문자열이 0으로 대체되었기 때문에 계산 자체는 정상적으로 수행되었을 가능성이 큽니다.

다만, 이 경고를 완전히 제거하고 정확한 처리를 위해, IFNULL 또는 NULLIF와 같은 함수로 빈 문자열을 NULL로 변환한 후 COALESCE로 처리하는 방법을 사용할 수 있습니다. 이렇게 하면 경고 없이 값을 올바르게 처리할 수 있습니다.

NULLIF(bill1, ''): 이 함수는 bill1 값이 빈 문자열인 경우 NULL을 반환합니다. 그렇지 않으면 원래 값을 반환합니다. 이 방법으로 빈 문자열을 NULL로 처리하여 DECIMAL 변환에서 발생하는 경고를 피할 수 있습니다.

COALESCE: 여전히 값이 NULL인 경우 0을 반환하여 계산이 진행되도록 합니다.

이 수정된 쿼리를 실행하면 경고 없이 sum_receivable을 정확하게 계산할 수 있을 것입니다.

위의 코드에서 

'COALESCE(CAST(REPLACE(NULLIF(bill1, ''), ',', '') AS DECIMAL(15, 2)), 0) +'

이 문법을 해독할줄 알면 되는 것이었네요^^

 

COALESCE 는 어떤 의미의 함수인가?

COALESCE 함수는 SQL에서 자주 사용되는 함수로, 전달된 인자들 중에서 NULL이 아닌 첫 번째 값을 반환하는 역할을 합니다. 여러 개의 인자를 순서대로 평가하여, 그 중에서 첫 번째로 NULL이 아닌 값을 찾아 반환합니다. 만약 모든 인자가 NULL이라면 NULL을 반환합니다.

예시:
SELECT COALESCE(NULL, NULL, 'First Non-Null', 'Another Value');
위 SQL 구문은 'First Non-Null'을 반환합니다. 이유는 앞의 두 인자(NULL, NULL)가 NULL이므로, 세 번째 인자 'First Non-Null'이 반환됩니다.

사용 사례:
NULL 대체: 데이터베이스에서 NULL 값을 다른 기본값으로 대체하는 데 사용됩니다.

SELECT COALESCE(column_name, 'Default Value') FROM table_name;
여기서 column_name이 NULL인 경우 'Default Value'가 반환됩니다.

다중 필드 우선순위 처리: 여러 열 중 첫 번째로 NULL이 아닌 값을 선택하고자 할 때 사용됩니다.

SELECT COALESCE(phone_home, phone_mobile, phone_work) AS primary_phone FROM contacts;
이 예시에서, phone_home이 NULL이 아니면 그 값을 반환하고, 그렇지 않으면 phone_mobile, 그다음에 phone_work를 차례로 평가하여 NULL이 아닌 첫 번째 값을 반환합니다.

COALESCE는 한국어로 "코얼레스크" 또는 "코얼리스"라고 읽을 수 있습니다. 정확한 발음은 사람에 따라 약간 다를 수 있지만, 보통 "코얼레스크"라고 많이 읽습니다.