SELECT b.cust_name,c.item_name,A.item_type, A.chgo_date,A.cust_code,A.item_code,A.chgo_qnty,A.chgo_dann,A.chgo_amnt,(chgo_amnt*0.1) as chgo_vats,
(A.chgo_amnt+(chgo_amnt*0.1)) as AMNT_VATS,(chgo_amnt) as TOTA_AMNT,(chgo_amnt*0.1) as TOTA_VATS,(A.chgo_amnt+(chgo_amnt*0.1)) as AMNT_VATSS
FROM ITEM_CHGO as A with(nolock)
LEFT OUTER JOIN
CUST_INFO B
ON a.cust_code = b.cust_Code
left outer join
item_info c
on a.item_code = c.item_code
where c.cust_code = '0009'
SELECT cust_name , item_name , chgo_date , cust_code , item_code , chgo_qnty , chgo_dann , chgo_amnt , chgo_vats , amnt_vats -- 금액 한글 변환 -- , REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( CONCAT ( SUBSTRING(v, 1, 1), CASE SUBSTRING(v, 1, 1) WHEN 0 THEN '' ELSE '천' END , SUBSTRING(v, 2, 1), CASE SUBSTRING(v, 2, 1) WHEN 0 THEN '' ELSE '백' END , SUBSTRING(v, 3, 1), CASE SUBSTRING(v, 3, 1) WHEN 0 THEN '' ELSE '십' END , SUBSTRING(v, 4, 1), CASE SUBSTRING(v, 1, 4) WHEN 0 THEN '' ELSE '조' END , SUBSTRING(v, 5, 1), CASE SUBSTRING(v, 5, 1) WHEN 0 THEN '' ELSE '천' END , SUBSTRING(v, 6, 1), CASE SUBSTRING(v, 6, 1) WHEN 0 THEN '' ELSE '백' END , SUBSTRING(v, 7, 1), CASE SUBSTRING(v, 7, 1) WHEN 0 THEN '' ELSE '십' END , SUBSTRING(v, 8, 1), CASE SUBSTRING(v, 5, 4) WHEN 0 THEN '' ELSE '억' END , SUBSTRING(v, 9, 1), CASE SUBSTRING(v, 9, 1) WHEN 0 THEN '' ELSE '천' END , SUBSTRING(v,10, 1), CASE SUBSTRING(v,10, 1) WHEN 0 THEN '' ELSE '백' END , SUBSTRING(v,11, 1), CASE SUBSTRING(v,11, 1) WHEN 0 THEN '' ELSE '십' END , SUBSTRING(v,12, 1), CASE SUBSTRING(v, 9, 4) WHEN 0 THEN '' ELSE '만' END , SUBSTRING(v,13, 1), CASE SUBSTRING(v,13, 1) WHEN 0 THEN '' ELSE '천' END , SUBSTRING(v,14, 1), CASE SUBSTRING(v,14, 1) WHEN 0 THEN '' ELSE '백' END , SUBSTRING(v,15, 1), CASE SUBSTRING(v,15, 1) WHEN 0 THEN '' ELSE '십' END , SUBSTRING(v,16, 1) , '원정' ) , '1', '일'), '2', '이'), '3', '삼'), '4', '사'), '5', '오') , '6', '육'), '7', '칠'), '8', '팔'), '9', '구'), '0', '' ) AS amnt_vats_korean FROM (SELECT b.cust_name , c.item_name , a.chgo_date , a.cust_code , a.item_code , a.chgo_qnty , a.chgo_dann , a.chgo_amnt AS chgo_amnt , a.chgo_amnt * 0.1 AS chgo_vats , a.chgo_amnt * 1.1 AS amnt_vats , RIGHT(CONCAT(REPLICATE('0', 16), a.chgo_amnt * 1.1), 16) v FROM item_chgo AS a WITH(NOLOCK) LEFT OUTER JOIN cust_info b ON a.cust_code = b.cust_Code LEFT OUTER JOIN item_info c ON a.item_code = c.item_code AND a.cust_code = c.cust_Code -- 추가 WHERE a.cust_code = '0009' ) a ; -- 참조 : http://gurubee.net/lecture/2224