java 查询db2_DB2查询语句

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数据类型，去除小数点后转换成字符串类型。