TJoin and TMap and BigDecimal with a precision specified

One Star

TJoin and TMap and BigDecimal with a precision specified

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.
One Star

Re: TJoin and TMap and BigDecimal with a precision specified

With further testing ... with a small example I could get this work (comparing number(14,4) .. where the source and destination table definitions were exactly the same.
When the table definitions differed was when I ran into problems. (wondered if items were lined up by column number within table or by columns name .. )
I'm pretty new at this ... maybe it's just me ...
Community Manager

Re: TJoin and TMap and BigDecimal with a precision specified

Hello Allan
I have tried to map BigDecimal date type on tMap and it works fine on TOS3.1.0.
You can map it with String data type.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: TJoin and TMap and BigDecimal with a precision specified

Hi Shong,
Thankyou for your reply.
I tried map with string data type but get compile time error as per:
"Type mismatch: cannot convert from BigDecimal to String"
I wanted to use a Talend Process to
1./ compare source and destination query results by key columns to identify required inserts and updates
2./ where the record keys match I wanted to check all attributes to see if I could skip the update where all attributes match ..
i.e. only perform the inserts and updates that are required.
I'll see if I can post an image of the process.
best regarsds, Allan.
One Star

Re: TJoin and TMap and BigDecimal with a precision specified

image as per here
One Star

Re: TJoin and TMap and BigDecimal with a precision specified

(first jpg too large ..will try again)
One Star

Re: TJoin and TMap and BigDecimal with a precision specified

Maybe a related question is:
A TOracleInput component has properties for both a Table Name and for a Query ..
It seems that you can just provide the query and leave the table name empty?
One Star

Re: TJoin and TMap and BigDecimal with a precision specified

Seem to have hit this problem:
Starting job Copy_of_Transfer_dbMap_SPD_MTH_PROD_to_PPDM at 15:20 15/06/2009.
Exception in thread "main" java.lang.Error: Unresolved compilation problems:
Type mismatch: cannot convert from BigDecimal to long
Type mismatch: cannot convert from BigDecimal to Double
Type mismatch: cannot convert from BigDecimal to Double
Type mismatch: cannot convert from BigDecimal to Double
Type mismatch: cannot convert from BigDecimal to Double
at talenddemosjava.copy_of_transfer_dbmap_spd_mth_prod_to_ppdm_0_1.Copy_of_Transfer_dbMap_SPD_MTH_PROD_to_PPDM.tOracleInput_3Process(Copy_of_Transfer_dbMap_SPD_MTH_PROD_to_PPDM.java:13673)

I cut this down to a simpler process ... found that if I specified Oracle 9 I got further ...
Source database is
Oracle8i Enterprise Edition Release 8.1.7.2.0
One Star

Re: TJoin and TMap and BigDecimal with a precision specified

tried starting again .. with a simpler process ..
Output as per below ... (I wasn't expecting any rows in tLogRow_3 as the data matches up OK between source and destination ..)
Starting job test6 at 15:30 15/06/2009.
99_X.01.01/Hu|PDEN_PR_STR_FORM|SANTOS|ALLOCATED|PRODUCTION|MONTH|01/04/2009|0|Wed Apr 01 00:00:00 CST 2009|dd/mm/yyyy|m3||m3||m3||0|day
99_X.01.01/Ti|PDEN_PR_STR_FORM|SANTOS|ALLOCATED|PRODUCTION|MONTH|01/04/2009|0|Wed Apr 01 00:00:00 CST 2009|dd/mm/yyyy|m3|0.0000|m3||m3||0|day
990_X.01.01/Pa|PDEN_PR_STR_FORM|SANTOS|ALLOCATED|PRODUCTION|MONTH|01/04/2009|0|Wed Apr 01 00:00:00 CST 2009|dd/mm/yyyy|m3|0.0000|m3||m3||0|day
991_X.01.01/Ep|PDEN_PR_STR_FORM|SANTOS|ALLOCATED|PRODUCTION|MONTH|01/04/2009|0|Wed Apr 01 00:00:00 CST 2009|dd/mm/yyyy|m3||m3||m3||27|day
991_X.01.01/Pa|PDEN_PR_STR_FORM|SANTOS|ALLOCATED|PRODUCTION|MONTH|01/04/2009|0|Wed Apr 01 00:00:00 CST 2009|dd/mm/yyyy|m3|568622.5417|m3||m3||27|day
993_X.01.01/Pa|PDEN_PR_STR_FORM|SANTOS|ALLOCATED|PRODUCTION|MONTH|01/04/2009|0|Wed Apr 01 00:00:00 CST 2009|dd/mm/yyyy|m3|207513.7000|m3||m3||30|day
994_X.01.01/Pa|PDEN_PR_STR_FORM|SANTOS|ALLOCATED|PRODUCTION|MONTH|01/04/2009|0|Wed Apr 01 00:00:00 CST 2009|dd/mm/yyyy|m3|0.0000|m3||m3||0|day
986_X.01.01/Pa|PDEN_PR_STR_FORM|SANTOS|ALLOCATED|PRODUCTION|MONTH|01/04/2009|0|Wed Apr 01 00:00:00 CST 2009|dd/mm/yyyy|m3|137344.4616|m3||m3||30|day
987_X.01.01/Pa|PDEN_PR_STR_FORM|SANTOS|ALLOCATED|PRODUCTION|MONTH|01/04/2009|0|Wed Apr 01 00:00:00 CST 2009|dd/mm/yyyy|m3|0.0000|m3||m3||0|day
988_X.01.01/Pa|PDEN_PR_STR_FORM|SANTOS|ALLOCATED|PRODUCTION|MONTH|01/04/2009|0|Wed Apr 01 00:00:00 CST 2009|dd/mm/yyyy|m3|0.0000|m3||m3||0|day
Community Manager

Re: TJoin and TMap and BigDecimal with a precision specified

Hello
It seems that you can just provide the query and leave the table name empty?

After you define the shema and type in the table name in Table Name field, then click on the 'Guess query' button to generate query statament automatically.
When you use tOracleInput compopnent, set Type as BigDecimal
When you use tOracleOutput component, set the Type as BigDecimal and DB Type as DEC.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: TJoin and TMap and BigDecimal with a precision specified

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 ?
One Star

Re: TJoin and TMap and BigDecimal with a precision specified

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>
One Star

Re: TJoin and TMap and BigDecimal with a precision specified

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)
One Star

Re: TJoin and TMap and BigDecimal with a precision specified

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 )
One Star

Re: TJoin and TMap and BigDecimal with a precision specified

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
One Star

Re: TJoin and TMap and BigDecimal with a precision specified

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

Re: TJoin and TMap and BigDecimal with a precision specified

Lijo ,
Is there any simplier way than using routines.
Seven Stars

Re: TJoin and TMap and BigDecimal with a precision specified

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.
Seventeen Stars

Re: TJoin and TMap and BigDecimal with a precision specified

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

Re: TJoin and TMap and BigDecimal with a precision specified

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.
Four Stars

Re: TJoin and TMap and BigDecimal with a precision specified

I switched it back to FLOAT and it works fine, but I am curious if this is a bug? Why can I not compare two BigDecimals in a tMap?
Seven Stars

Re: TJoin and TMap and BigDecimal with a precision specified

Using BigDecimals for the lookup key works fine for me in v4.1.3 even if the two columns have different length and precision...
Four Stars

Re: TJoin and TMap and BigDecimal with a precision specified

Weird, I am using 4.2M4.
Seven Stars

Re: TJoin and TMap and BigDecimal with a precision specified

You should never use Milestone releases other than for beta-testing given the potential for outstanding bugs. I strongly suggest you urgently upgrade to v4.2.2 and try again.
Four Stars

Re: TJoin and TMap and BigDecimal with a precision specified

I got this before 4.2.2 was released, I'll upgrade now.
Seven Stars

Re: TJoin and TMap and BigDecimal with a precision specified

I just encountered this problem using v4.1.3. It's caused by the scale (i.e. precision i.e. decimal places) of the BigDecimal amounts. If I force the two amounts to have the same scale, even if no rounding is required, then the lookup works. I no longer have the test I used for my post on 29 June but I doubt I had main and lookup with different scale. I've submitted a bug.
Four Stars

Re: TJoin and TMap and BigDecimal with a precision specified

I forced both of them to have the same scale, but of course this is to the 8th place (which is what we use in our DB), perhaps I need to cut it down to 3 places to enforce the matches. Another weird thing is that both amounts when I was testing this was .0 all the way, maybe I'm wrong.
Seven Stars

Re: TJoin and TMap and BigDecimal with a precision specified

BigDecimals don't lose accuracy like floating-point variables do so they should match correctly if each side has the same number of places. Remember that the actual scale of the Java variable is not necessarily (even unlikely to be) the same as the schema precision. Did you use .setScale to force the scale? You can see the exact scale by printing it (System.out.print) or use .scale() to get the number.