--百分比正确显示的函数--

?

CREATE FUNCTION orafun.TO_CHAR (num decimal(31,2)) RETURNS VARCHAR(60) LANGUAGE?
SQL CONTAINS SQL SPECIFIC TOCHARDECIMAL1 NO EXTERNAL ACTION DETERMINISTIC?
RETURN case when num <0 and ( length(char(num))-locate('.',char(num))) >1?
then '-' | |varchar(rtrim(char(bigint(num*(-1)))) | |substr( char(num*(-1)),locate('.',char(num*(-1))),length(char(num*(-1)))-locate('.',char(num*(-1)))))?
when ( length(char(num))-locate('.',char(num))) >1 then varchar(rtrim(char(bigint(num))) | |substr(?
char(num),locate('.',char(num)),length(char(num))-locate('.',char(num))))?
else varchar(char(bigint(num))) end;?

?

?-- 用函数

select orafun.TO_CHAR(1*1.0*100/50)||'%'? from CM_CUST_INFO_MODIFY_QUALITY cq,CM_CARD_SIGN_ADD_QUALITY csq
?group by cq.OPERATOR_ID

?

-- 在ireport中可以加上上'%'，ＳＱＬ中不加＇％＇因为ｃａｓｔ会影响效率．

?select distinct cq.OPERATOR_ID,
?(select count(*) from? CM_CUST_INFO_MODIFY_QUALITY? ) as CUST_QUA_NUM,
?
?cast(
?(select count(*) from? CM_CUST_INFO_MODIFY_QUALITY? )*1.0*100/(select count(*) from CM_CUST_INFO_MODIFY_ENTITY ce ,CM_CUST_INFO_MODIFY cm where ce.CUST_MODIFY_ID = cm.ID? )
?as decimal(4,2)
?)
? as CUST_QUA_RATE,?
?(select count(*) from CM_CARD_SIGN_ADD_QUALITY ) as CARD_QUA_NUM,
?
?cast(
?(select count(*) from CM_CARD_SIGN_ADD_QUALITY )*1.0*100/(select count(*) from CM_CARD_SIGN_ADD ca?? )
?as decimal(4,2)
?)
?as CARD_QUA_RATE
?from CM_CUST_INFO_MODIFY_QUALITY cq
?group by cq.OPERATOR_ID

?

?

－－一种不太好的写法，可能会带前导００

?select distinct
?
rtrim(
?cast
?(
?
?cast
?(
?(select count(*) from? CM_CUST_INFO_MODIFY_QUALITY? )*1.0*100/(select count(*) from CM_CUST_INFO_MODIFY_ENTITY ce ,CM_CUST_INFO_MODIFY cm where ce.CUST_MODIFY_ID = cm.ID? )
? as decimal(5,3)
?)
?as char(50)
?)
?)
?||'%'
?
?
?
?as CUST_QUA_RATE
?
?from CM_CUST_INFO_MODIFY_QUALITY cq,CM_CARD_SIGN_ADD_QUALITY csq
?group by cq.OPERATOR_ID