TJoin and TMap and BigDecimal with a precision specified

[Anonymous]

Dear Forum and Talend,
I was wondering if anyone had ever come across a problem with using TJoin or TMap to join result sets where one of the join columns is a BigDecimal with a precision specified (e.g. in Oracle tha columns is defined as number(14,4) )
Both TJoin and TMap seem to not match on this type of column .. i.e. some of the rows which do actually match come through the non matching result set ...
???
Has anyone else seen this or encountered this? or have a solution to this?
thanks, Allan.

[Anonymous]

I'm assuming I don't need to put in order by type clause in the various queries ... i.e. am assuming that tJoin will work without need for order by clause in query ?

[Anonymous]

Maybe in't all in my court after all:
SQL> set NUMF 99999999.999999999
SQL> SELECT
2 GAS_PROD_VOLUME_M3 as GAS_VOLUME
3 FROM
4 SANTOS.OFM_WELL_COMPLETIONS OWC,
5 SANTOS.WELL_CMPLTN_MONTHLY_PROD WCMP
6 WHERE
7 owc.WELL_COMPLETION_S = WCMP.WELL_COMPLETION_S
8 and WCMP.PRODUCTION_MONTH >= to_Date('01/APR/2009','dd/mon/yyyy')
9 and WCMP.PRODUCTION_MONTH <= to_Date('30/APR/2009','dd/mon/yyyy')
10 and OWC.EC_PERF_INTERVAL_CODE = '1008_X.01.01/Pa'
11 order by
12 OWC.EC_PERF_INTERVAL_CODE;
GAS_VOLUME
-------------------
368718.893666667

SQL> set NUMF 99999999.999999999
SQL> select
2 GAS_VOLUME
3 from
4 PPDM38.PDEN_VOL_SUMMARY
5 where
6 EFFECTIVE_DATE >= to_Date('01/APR/2009','dd/mon/yyyy')
7 and EFFECTIVE_DATE <= to_Date('30/APR/2009','dd/mon/yyyy')
8 and pden_id = '1008_X.01.01/Pa'
9 order by PDEN_ID ;
GAS_VOLUME
-------------------
368718.893700000
SQL>

[Anonymous]

This was all my own problem rather than the tool .. (sorry Talend that I doubted you ..)
This answer may be of much use to other people nethertheless ..
as data compare is a big part of intergration !!
i.e. if the source end has more precision that the destination then place suitable round function calls in the source end sql ..
i.e. my source end SQL is now as per:

SELECT
OWC.EC_PERF_INTERVAL_CODE as PDEN_ID,
'PDEN_PR_STR_FORM' as PDEN_TYPE,
'SANTOS' as PDEN_SOURCE,
'ALLOCATED' as VOLUME_METHOD,
'PRODUCTION' as ACTIVITY_TYPE,
'MONTH' as PERIOD_TYPE,
To_Char(WCMP.PRODUCTION_MONTH,'dd/mm/yyyy') as VOLUME_DATE,
to_number('0') as AMENDMENT_SEQ_NO,
WCMP.PRODUCTION_MONTH as EFFECTIVE_DATE,
'dd/mm/yyyy' as DATE_FORMAT_DESC,
'm3' as GAS_VOLUME_OUOM,
round(GAS_PROD_VOLUME_M3,4) as GAS_VOLUME,
'm3' as WATER_VOLUME_OUOM,
round(WATER_PROD_VOLUME_M3,4) as WATER_VOLUME,
'm3' as OIL_VOLUME_OUOM,
round(OIL_PROD_VOLUME_M3,4) as OIL_VOLUME,
round(PRODUCTIVE_DAYS,0) as period_on_production,
'day' as period_on_production_ouom
FROM
SANTOS.OFM_WELL_COMPLETIONS OWC,
SANTOS.WELL_CMPLTN_MONTHLY_PROD WCMP
WHERE
owc.WELL_COMPLETION_S = WCMP.WELL_COMPLETION_S
and WCMP.PRODUCTION_MONTH >= to_Date('01/APR/2009','dd/mon/yyyy')
and WCMP.PRODUCTION_MONTH <= to_Date('30/APR/2009','dd/mon/yyyy')
order by
OWC.EC_PERF_INTERVAL_CODE
The order by is somewhat un required but may be useful ...
This is intended to line up with the PPDM table called PPDM38.PDEN_VOL_SUMMARY
SQL> desc PPDM38.PDEN_VOL_SUMMARY
Name Null? Type
----------------------------------------- -------- --------------
PDEN_ID NOT NULL VARCHAR2(40)
PDEN_TYPE NOT NULL VARCHAR2(30)
PDEN_SOURCE NOT NULL VARCHAR2(20)
VOLUME_METHOD NOT NULL VARCHAR2(20)
ACTIVITY_TYPE NOT NULL VARCHAR2(20)
PERIOD_TYPE NOT NULL VARCHAR2(20)
VOLUME_DATE NOT NULL VARCHAR2(12)
AMENDMENT_SEQ_NO NOT NULL NUMBER(8)
ACTIVE_IND VARCHAR2(1)
AMEND_REASON VARCHAR2(20)
BOE_CUM_VOLUME NUMBER(16,4)
BOE_VOLUME NUMBER(14,4)
BOE_VOLUME_OUOM VARCHAR2(20)
BOE_YTD_VOLUME NUMBER(14,4)
CO2_CUM_VOLUME NUMBER(16,4)
CO2_VOLUME NUMBER(14,4)
CO2_VOLUME_OUOM VARCHAR2(20)
CO2_YTD_VOLUME NUMBER(14,4)
DATE_FORMAT_DESC VARCHAR2(20)
EFFECTIVE_DATE DATE
EXPIRY_DATE DATE
GAS_CUM_VOLUME NUMBER(16,4)
GAS_QUALITY NUMBER(7,2)
GAS_QUALITY_OUOM VARCHAR2(20)
GAS_VOLUME NUMBER(14,4)
GAS_VOLUME_OUOM VARCHAR2(20)
GAS_YTD_VOLUME NUMBER(14,4)
INJECTION_CYCLE NUMBER(4)
INJECTION_PRESSURE NUMBER(9,2)
INJECTION_PRESSURE_OUOM VARCHAR2(20)
INVENTORY_CLOSE_BALANCE NUMBER(14,4)
INVENTORY_OPEN_BALANCE NUMBER(14,4)
INVENTORY_PRODUCT VARCHAR2(20)
INVENT_CLOSE_BAL_OUOM VARCHAR2(20)
INVENT_OPEN_BAL_OUOM VARCHAR2(20)
NGL_CUM_VOLUME NUMBER(16,4)
NGL_VOLUME NUMBER(14,4)
NGL_VOLUME_OUOM VARCHAR2(20)
NGL_YTD_VOLUME NUMBER(14,4)
NITROGEN_CUM_VOLUME NUMBER(16,4)
NITROGEN_VOLUME NUMBER(14,4)
NITROGEN_VOLUME_OUOM VARCHAR2(20)
NITROGEN_YTD_VOLUME NUMBER(14,4)
NO_OF_GAS_WELLS NUMBER(7)
NO_OF_INJECTION_WELLS NUMBER(7)
NO_OF_OIL_WELLS NUMBER(7)
OIL_CUM_VOLUME NUMBER(16,4)
OIL_QUALITY NUMBER(7,2)
OIL_QUALITY_OUOM VARCHAR2(20)
OIL_VOLUME NUMBER(14,4)
OIL_VOLUME_OUOM VARCHAR2(20)
OIL_YTD_VOLUME NUMBER(14,4)
PERIOD_ON_INJECTION NUMBER(4)
PERIOD_ON_INJECTION_OUOM VARCHAR2(20)
PERIOD_ON_PRODUCTION NUMBER(4)
PERIOD_ON_PRODUCTION_OUOM VARCHAR2(20)
POSTED_DATE DATE
PPDM_GUID VARCHAR2(38)
PRIMARY_ALLOWABLE NUMBER(14,4)
PRIMARY_ALLOWABLE_OUOM VARCHAR2(20)
PRIMARY_PRODUCT VARCHAR2(20)
PROJECT_ID VARCHAR2(20)
REMARK VARCHAR2(2000)
REPORT_IND VARCHAR2(1)
SOURCE VARCHAR2(20)
SULPHUR_CUM_VOLUME NUMBER(16,4)
SULPHUR_VOLUME NUMBER(14,4)
SULPHUR_VOLUME_OUOM VARCHAR2(20)
SULPHUR_YTD_VOLUME NUMBER(14,4)
VOLUME_PERIOD NUMBER(12)
VOLUME_PERIOD_OUOM VARCHAR2(20)
WATER_CUM_VOLUME NUMBER(16,4)
WATER_VOLUME NUMBER(14,4)
WATER_VOLUME_OUOM VARCHAR2(20)
WATER_YTD_VOLUME NUMBER(14,4)
ROW_CHANGED_BY VARCHAR2(30)
ROW_CHANGED_DATE DATE
ROW_CREATED_BY VARCHAR2(30)
ROW_CREATED_DATE DATE
ROW_QUALITY VARCHAR2(20)

[Anonymous]

i.e. The importance of Oracle round function calls in comparing numeric attributes with differing precisions between source and destination ..
(I guess this only applies where the source system can have more precision than the destination )

[_AnonymousUser]

Hi,
I want to know how to round a source value to a precision of 2.
For eg. If source value 13.45672 the it sud give 13.46 or 13.45 neglect it to 2 decimal point (anything is ok).
Thanx in adv

[Anonymous]

Hi
write a routine
double roundTwoDecimals(double d) {
DecimalFormat twoDForm = new DecimalFormat("#.##");
return Double.valueOf(twoDForm.format(d));
}
Call this routine for rounding off

[_AnonymousUser]

Lijo ,
Is there any simplier way than using routines.

[alevy]

If your source value is a double or float, you can use ((Long)Math.round(<var>*100)).doubleValue()/100
If your source value is a BigDecimal, you can use <var>.setScale(2,java.math.RoundingMode.HALF_EVEN)
See http://java.sun.com/javase/6/docs/api/java/math/RoundingMode.html for an explanation and examples of the different rounding methods.

[Anonymous]

hi all
please, use a new topic for a new question
It's much easier and helpful to retrieve topic through "search forum option".
thanks
regards
laurent

[jkrfs]

I'm having a similar issue. I am using a lookup and it works when I don't have the BigDecimal look up present. The issue is that before I had it as a float and it worked perfectly fine. I switched it to BigDecimal and now its not doing the look up correctly. If I remove the BigDecimal variable (tempOptionStrike/option_strike) then it works again.
The length and precision is the same throughout the job, so I am not sure what I might be doing wrong. Any help would be appreciated.