Untitled

if Object_id('tempdb.dbo.#A') is not NULL drop table #A
SELECT chdrnum,LIFE,COVERAGE,crtable,pay_year,crrcd,cntcurr,nsumins,ninstamt,nannprem,lifename,lifesex,lifeage,lifeid,riskstatus,premstatus,srcebus into #A FROM LASUC WHERE crtable in ('5IQ8','6U5U','6U7N','6U3L','6UB9','6UBD','6UBH','6U7R','6U3N','5I6H','6UBI','6UBJ','6UBN','6UBL') AND riskstatus='IF' ORDER BY CRRCD 

SELECT * FROM #A


ALTER TABLE #A ADD PD VARCHAR(256) ,LRKCLS  VARCHAR(4) --險種中文名稱
ALTER TABLE #A ADD fx DECIMAL(10,0) --匯率
ALTER TABLE #A add 台幣保費 DECIMAL(10,0)
ALTER TABLE #A add 台幣保額 DECIMAL(10,0)


UPDATE #A SET fx=IIF(CNTCURR='USD',32,IIF(CNTCURR='CNT',4.5,IIF(CNTCURR='AUD',21,1)))--匯率
UPDATE #A SET PD =B.ZPLONGC FROM #A A JOIN UCFO B ON A.CRTABLE=B.POLPLN --險種中文名稱
UPDATE #A SET 台幣保費 = ninstamt *fx   --台幣數值
UPDATE #A SET 台幣保額 = nsumins * fx   --台幣數值