/*
在 db2 forum 看到一個問題：
提問的人想要將 2.00000000000000E+010 轉成 readable、
meaningful 2x10^10。
And here is my recursive sql
※用 DB2 9 的 New Datatype Decfloat，
在 decimal 型態轉 char 很方便
*/
/* using Recursive SQL，一直除10，除到 >= 1 */
WITH N(ORGVAL,RESULTVAL,DIVVAL,PLUS)
AS
(SELECT ORGVAL,ORGVAL / DIVVAL RESULTVAL,DIVVAL,1 PLUS
FROM (SELECT DECFLOAT(2.00000000000000E+010) ORGVAL,
10 DIVVAL
FROM SYSIBM.SYSDUMMY1) T
UNION ALL
SELECT N.ORGVAL,N.RESULTVAL / 10,DIVVAL,N.PLUS + 1
FROM N
WHERE N.RESULTVAL / 10 >= 1
)
SELECT RTRIM(CHAR(RESULTVAL))||'x'||
RTRIM(CHAR(DIVVAL))||'^'||
RTRIM(CHAR(PLUS))
FROM (SELECT RESULTVAL,DIVVAL,PLUS,
ROW_NUMBER() OVER(ORDER BY PLUS DESC) OID
FROM N) T1
WHERE OID = 1;
/* 2.00000000000000E+010的結果 */
2x10^10
/* 測試 0.125040000000000E+010 */
WITH N(ORGVAL,RESULTVAL,DIVVAL,PLUS)
AS
(SELECT ORGVAL,ORGVAL / DIVVAL RESULTVAL,DIVVAL,1 PLUS
FROM (SELECT DECFLOAT(0.125040000000000E+010) ORGVAL,
10 DIVVAL
FROM SYSIBM.SYSDUMMY1) T
UNION ALL
SELECT N.ORGVAL,N.RESULTVAL / 10,DIVVAL,N.PLUS + 1
FROM N
WHERE N.RESULTVAL / 10 >= 1
)
SELECT RTRIM(CHAR(RESULTVAL))||'x'||
RTRIM(CHAR(DIVVAL))||'^'||
RTRIM(CHAR(PLUS))
FROM (SELECT RESULTVAL,DIVVAL,PLUS,
ROW_NUMBER() OVER(ORDER BY PLUS DESC) OID
FROM N) T1
WHERE OID = 1;
/* 0.125040000000000E+010的結果 */
1.2504x10^9
/* 測試 1.39080000000000E+010 */
WITH N(ORGVAL,RESULTVAL,DIVVAL,PLUS)
AS
(SELECT ORGVAL,ORGVAL / DIVVAL RESULTVAL,DIVVAL,1 PLUS
FROM (SELECT DECFLOAT(1.39080000000000E+010) ORGVAL,
10 DIVVAL
FROM SYSIBM.SYSDUMMY1) T
UNION ALL
SELECT N.ORGVAL,N.RESULTVAL / 10,DIVVAL,N.PLUS + 1
FROM N
WHERE N.RESULTVAL / 10 >= 1
)
SELECT RTRIM(CHAR(RESULTVAL))||'x'||
RTRIM(CHAR(DIVVAL))||'^'||
RTRIM(CHAR(PLUS))
FROM (SELECT RESULTVAL,DIVVAL,PLUS,
ROW_NUMBER() OVER(ORDER BY PLUS DESC) OID
FROM N) T1
WHERE OID = 1;
/* 1.39080000000000E+010的結果 */
1.3908x10^10
