VALUE:
WITH AA AS(
select a499.MERCHANT_ID,a499.TERMINAL_ID,a499.trans_date,count(*) as count_num,SUM(a499.trans_amount) as count_sum
from DBAT.DTATA499 a499
where a499.MERCHANT_ID IS NOT NULL and a499.INSURANCE_ID = ':INSURANCE_ID' and a499.trans_date=':TRANS_DATE' AND a499.MERCHANT_ID=':MERCHANT_ID'
group by a499.MERCHANT_ID,a499.TERMINAL_ID,a499.trans_date
),
BB AS(
SELECT DISTINCT AA.MERCHANT_ID,b001.POS_TYPE,z020.FELD_NAME,AA.trans_date,AA.TERMINAL_ID,AA.count_num,AA.count_sum
FROM DBAC.DTACB001 b001
join DBAG.DTAGZ020 z020 on b001.POS_TYPE=z020.FELD_CONT and z020.DBK_NO = 'DJ' And Z020.FELD_NO ='POS_KD'
right JOIN AA ON AA.MERCHANT_ID=b001.MERCHANT_ID
WHERE b001.POS_TYPE=':POS_TYPE'
)
select FELD_NAME,TERMINAL_ID,MERCHANT_ID,trans_date,char(bigint(count_num)) count_num,rtrim(char(bigint(count_sum)))||''||translate(rtrim(translate(right(char(count_sum),4)))) count_sum,POS_TYPE from BB with UR
其中trans_date,char(bigint(count_num)) 是表示将数据库中BIGDICEMAL数据类型，去除小数点后转换成字符串类型。