SELECT TO_CHAR(avg_rate) chris
, LENGTH(TO_CHAR(avg_rate)) len , LENGTH(SUBSTR(TO_CHAR(avg_rate),INSTR(TO_CHAR(avg_rate),'.'))) DEC
,LENGTH(TO_CHAR(avg_rate)) - LENGTH(SUBSTR(TO_CHAR(avg_rate),INSTR(TO_CHAR(avg_rate),'.'))) b4
FROM GL_TRANS_RATE_TST r
0.024967355 | 40 | 40 | 0 |
12345.12346 | 21 | 16 | 5 |
123456789.1 | 25 | 16 | 9 |
122.1001221 | 14 | 11 | 3 |
1.350074254 | 12 | 11 | 1 |
123457890 | 9 | 9 | 0 |
1.23457E+14 | 25 | 10 | 15 |
1.23457E+17 | 28 | 10 | 18 |
48.34354082 | 39 | 37 | 2 |
Test results after running the session
(The datatypes specified are defined only in the mapping. In the target table it is defined as Oracle NUMBER datatype )
SELECT set_of_books_id,TO_CHAR(average_rate) ris
, LENGTH(TO_CHAR(average_rate)) len , LENGTH(SUBSTR(TO_CHAR(average_rate),INSTR(TO_CHAR(average_rate),'.'))) DEC
, LENGTH(SUBSTR(TO_CHAR(average_rate),1,INSTR(TO_CHAR(average_rate),'.'))) before1
FROM conv_ref_tst
ORDER BY 1
DECIMAL(28,12)
1 | .0249673551830981 | 17 | 17 | 1 |
2 | 12345.123456789 | 15 | 10 | 6 |
3 | 123456789.123457 | 16 | 7 | 10 |
4 | 122.1001221001 | 14 | 11 | 4 |
5 | 1.3500742541 | 12 | 11 | 2 |
6 | 123457890 | 9 | 9 | |
7 | 123456789012345 | 15 | 15 | |
8 | 123456789012346000 | 18 | 18 | |
9 | 48.3435408203774 | 16 | 14 | 3 |
DECIMAL(28,12) WITH high PRECISION
1 | .024967355183 | 13 | 13 | 1 |
2 | 12345.123456789012 | 18 | 13 | 6 |
3 | 123456789.123456789012 | 22 | 13 | 10 |
4 | 122.1001221001 | 14 | 11 | 4 |
5 | 1.3500742541 | 12 | 11 | 2 |
6 | 123457890 | 9 | 9 | |
7 | 123456789012345.123456789 | 25 | 10 | 16 |
9 | 48.343540820377 | 15 | 13 | 3 |
9 | 48.3435408203774 | 16 | 14 | 3 |
DECIMAL 28,28 WITH high PRECISION
1 | .0249673551830980992352499107 | 29 | 29 | 1 |
DECIMAL 15,15 WITH high PRECISION
1 | .024967355183098 | 16 | 16 | 1 |
DECIMAL 28,15 WITH high PRECISION
1 | .024967355183098 | 16 | 16 | 1 |
5 | 1.3500742541 | 12 | 11 | 2 |
9 | 48.343540820377357 | 18 | 16 | 3 |
DECIMAL 26,16 WITH high PRECISION
1 | .0249673551830981 | 17 | 17 | 1 |
5 | 1.3500742541 | 12 | 11 | 2 |
9 | 48.3435408203773566 | 19 | 17 | 3 |
DECIMAL 40,28 WITH high PRECISION
DECIMAL 28,28
1 | .0249673551830981 | 17 | 17 | 1 |
2 | 12345.123456789 | 15 | 10 | 6 |
3 | 123456789.123457 | 16 | 7 | 10 |
4 | 122.1001221001 | 14 | 11 | 4 |
5 | 1.3500742541 | 12 | 11 | 2 |
6 | 123457890 | 9 | 9 | |
7 | 123456789012345 | 15 | 15 | |
8 | 123456789012346000 | 18 | 18 | |
9 | 48.3435408203774 | 16 | 14 | 3 |
Powercenter server processes decimals as doubles or decimals.
If you dont enable High precision, informatica powercenter treates teh data as doubl e with precision of 15.
To enable High precision, Decimal datatype in the mapping should have a precision of 16 to 28. Then select Enable High Precision in the session properties
This post was really useful. It helped us solve a production issue. Thanks a ton.
ReplyDelete