General Page
This is one query ouput display problem :
- Remove leading zero
VARCHAR(00.10) or
CAST(00.10 AS VARCHAR(4)) or
CAST(00.10 AS CHAR(4))
V10 result is '.10'
V9 result is '0.10'
- No trailing decimal point
VARCHAR(1.)
CAST(1. AS VARCHAR(2)) or
CAST(1. AS CHAR(2))
V10 result is '1'
V9 result is '1.'
From db2 document, checked that db2 for LUW had not thus BIF_COMPATIBILITY parameter, but it had one parameter "dec_to_char_fmt" can be set to "V95", but with this parameter setting, it looks like still not get the expected result.
Like below example:
db2 "describe table blr.tb_blr_repo_master_daily"
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ACDATE SYSIBM DATE 4 0 Yes
RPT_DATE SYSIBM DATE 4 0 Yes
RGN_CD SYSIBM CHARACTER 2 0 Yes
BNK_CD SYSIBM SMALLINT 2 0 Yes
BOOK_TYPE_CD SYSIBM CHARACTER 2 0 Yes
REPO_DEAL_NO SYSIBM DECIMAL 16 0 Yes
REPO_TYPE SYSIBM CHARACTER 1 0 Yes
CCY_CD SYSIBM CHARACTER 3 0 Yes
CCY_GRP SYSIBM CHARACTER 6 0 Yes
TRADE_DATE SYSIBM DATE 4 0 Yes
VALUE_DATE SYSIBM DATE 4 0 Yes
MATURITY_DATE SYSIBM DATE 4 0 Yes
CASHFLOW_TENOR_CD SYSIBM CHARACTER 2 0 Yes
REPO_RATE SYSIBM DECIMAL 22 8 Yes
PRINCIPLE_AMT SYSIBM DECIMAL 19 4 Yes
PRINCIPLE_HKAMT SYSIBM DECIMAL 19 4 Yes
ACCRUED_INT_AMT SYSIBM DECIMAL 19 4 Yes
ACCRUED_INT_HKAMT SYSIBM DECIMAL 19 4 Yes
CASHFLOW_DATE_INT_AMT SYSIBM DECIMAL 19 4 Yes
CASHFLOW_DATE_INT_HKAMT SYSIBM DECIMAL 19 4 Yes
REPO_CPARTY SYSIBM VARCHAR 64 0 Yes
REPO_CPARTY_TYPE SYSIBM VARCHAR 10 0 Yes
CUS_CLASS_CD SYSIBM CHARACTER 2 0 Yes
INTRAGROUP_IND SYSIBM CHARACTER 1 0 Yes
CPARTY_NATION SYSIBM CHARACTER 32 0 Yes
ADJ_IND SYSIBM CHARACTER 1 0 Yes
AC_ITEM SYSIBM CHARACTER 10 0 Yes
PROD_CD SYSIBM CHARACTER 10 0 Yes
ACCRUED_INT_AC_ITEM SYSIBM CHARACTER 10 0 Yes
PROD_LVL_3_CD SYSIBM CHARACTER 6 0 Yes
CNY_CLASS_CD SYSIBM CHARACTER 2 0 Yes
SRC_TYPE_CD SYSIBM CHARACTER 3 0 Yes
ROW_ID SYSIBM CHARACTER 11 0 Yes
FOLDER_NAME SYSIBM VARCHAR 32 0 Yes
FOLDER_SH_NAME SYSIBM VARCHAR 10 0 Yes
REMAINING_TENOR_CD SYSIBM CHARACTER 2 0 Yes
36 record(s) selected.
$ db2 "SELECT DISTINCT A.REPO_RATE, NVL(CAST(A.REPO_RATE AS VARCHAR(24)) , '') AS REPO_RATE
> FROM BLR.TB_BLR_REPO_MASTER_DAILY A"
REPO_RATE REPO_RATE
------------------------ ------------------------
0.11141636 .11141636
0.00000000 .00000000
-1.11523891 -1.11523891
0.06366649 .06366649
0.89500000 .89500000
0.10824437 .10824437
0.00000034 .00000034
0.55000000 .55000000
7.20000000 7.20000000
3.50000000 3.50000000
8.50000000 8.50000000
1.08000000 1.08000000
9.60000000 9.60000000
12.00000000 12.00000000
0.60000000 .60000000
49.43422499 49.43422499
-1.15643552 -1.15643552
0.50000000 .50000000
0.85000000 .85000000
0.15000000 .15000000
0.80000000 .80000000
0.90000000 .90000000
0.10760613 .10760613
0.38000000 .38000000
0.00098068 .00098068
0.06185392 .06185392
3.35643552 3.35643552
1.92381351 1.92381351
0.04000000 .04000000
6.00000000 6.00000000
0.03454456 .03454456
4.00000000 4.00000000
2.66468931 2.66468931
1.20000000 1.20000000
0.65000000 .65000000
0.00002031 .00002031
1.13000000 1.13000000
0.11293317 .11293317
0.45000000 .45000000
1.05000000 1.05000000
0.03000000 .03000000
0.06453324 .06453324
0.43000000 .43000000
0.75000000 .75000000
-42.29134685 -42.29134685
0.40000000 .40000000
0.20000000 .20000000
1.10000000 1.10000000
0.70000000 .70000000
4.80000000 4.80000000
5.00000000 5.00000000
3.20000000 3.20000000
3.80000000 3.80000000
-1068.07836978 -1068.07836978
0.06000000 .06000000
3.00000000 3.00000000
6.12345600 6.12345600
0.25000000 .25000000
0.00002149 .00002149
8.40000000 8.40000000
-
0.06148922 .06148922
2.00000000 2.00000000
2.55000000 2.55000000
1.00000000 1.00000000
3.60000000 3.60000000
2.95000000 2.95000000
67 record(s) selected.
Customer want to get the same display result for the second column, but it looks like sometime missed leading zero before decimal point, like 0.06000000 .06000000 line, they want to get 0.06000000 instead of .06000000
After more testing and verify below solution, we can use appropriate formast with varchar_format() function, like below:
db2 "SELECT DISTINCT A.REPO_RATE, NVL(VARCHAR_FORMAT((DECIMAL(A.REPO_RATE,24,8)),'9999999990.00000000'), '') AS REPO_RATE FROM BLR.TB_BLR_REPO_MASTER_DAILY A"
REPO_RATE REPO_RATE
------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.00000000 0.00000000
1.05000000 1.05000000
0.06453324 0.06453324
1.10000000 1.10000000
0.04000000 0.04000000
0.00002149 0.00002149
2.66468931 2.66468931
-
0.06185392 0.06185392
3.00000000 3.00000000
0.43000000 0.43000000
0.89500000 0.89500000
0.00000034 0.00000034
6.12345600 6.12345600
8.40000000 8.40000000
4.80000000 4.80000000
0.00002031 0.00002031
1.92381351 1.92381351
7.20000000 7.20000000
0.11293317 0.11293317
0.80000000 0.80000000
0.10824437 0.10824437
0.03454456 0.03454456
1.20000000 1.20000000
0.06148922 0.06148922
12.00000000 12.00000000
3.20000000 3.20000000
0.06366649 0.06366649
3.60000000 3.60000000
3.50000000 3.50000000
0.75000000 0.75000000
0.85000000 0.85000000
1.13000000 1.13000000
2.95000000 2.95000000
0.50000000 0.50000000
6.00000000 6.00000000
0.00098068 0.00098068
0.15000000 0.15000000
0.11141636 0.11141636
0.20000000 0.20000000
0.70000000 0.70000000
0.06000000 0.06000000
0.90000000 0.90000000
3.80000000 3.80000000
1.08000000 1.08000000
2.00000000 2.00000000
2.55000000 2.55000000
5.00000000 5.00000000
0.55000000 0.55000000
0.10760613 0.10760613
-42.29134685 -42.29134685
0.45000000 0.45000000
49.43422499 49.43422499
0.60000000 0.60000000
3.35643552 3.35643552
0.03000000 0.03000000
-1.11523891 -1.11523891
-1.15643552 -1.15643552
1.00000000 1.00000000
0.40000000 0.40000000
9.60000000 9.60000000
4.00000000 4.00000000
0.65000000 0.65000000
0.38000000 0.38000000
0.25000000 0.25000000
-1068.07836978 -1068.07836978
8.50000000 8.50000000
67 record(s) selected.
| Format element | Description |
|---|---|
| 0 | Each 0 represents a significant digit. Leading zeros in a number are displayed as zeros. |
| 9 | Each 9 represents a significant digit. Leading zeros in a number are displayed as blanks. |
| MI | Suffix: If decimal-floating-point-expression is a negative number, a trailing minus sign (−) is included in the result. If decimal-floating-point-expression is a positive number, a trailing blank is included in the result. |
| S | Prefix: If decimal-floating-point-expression is a negative number, a leading minus sign (−) is included in the result. If decimal-floating-point-expression is a positive number, a leading plus sign (+) is included in the result. |
| PR | Suffix: If decimal-floating-point-expression is a negative number, a leading less than character (<) and a trailing greater than character (>) are included in the result. If decimal-floating-point-expression is a positive number, a leading space and a trailing space are included in the result. |
| $ | Prefix: A leading dollar sign ($) is included in the result. |
| , | Specifies that a comma be included in that location in the result. This comma is used as a group separator. |
| . | Specifies that a period be included in that location in the result. This period is used as a decimal point. |
Was this topic helpful?
Document Information
Modified date:
06 September 2018
UID
ibm10730579