topic Re: TJoin and TMap and BigDecimal with a precision specified in Talend Studio

TJoin and TMap and BigDecimal with a precision specified

Anonymous — Sat, 16 Nov 2024 13:55:10 GMT

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.

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Fri, 12 Jun 2009 03:23:56 GMT

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

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Fri, 12 Jun 2009 03:54:52 GMT

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

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Mon, 15 Jun 2009 06:18:01 GMT

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.

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Mon, 15 Jun 2009 06:20:06 GMT

image as per here

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Mon, 15 Jun 2009 06:21:17 GMT

(first jpg too large ..will try again)

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Mon, 15 Jun 2009 06:45:46 GMT

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?

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Mon, 15 Jun 2009 06:52:07 GMT

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

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Mon, 15 Jun 2009 07:13:43 GMT

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

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Mon, 15 Jun 2009 07:14:58 GMT

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

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Mon, 15 Jun 2009 07:16:55 GMT

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 ?

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Mon, 15 Jun 2009 07:30:03 GMT

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>

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Mon, 15 Jun 2009 07:43:41 GMT

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)

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Mon, 15 Jun 2009 07:55:02 GMT

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 )

Re: TJoin and TMap and BigDecimal with a precision specified

_AnonymousUser — Tue, 15 Mar 2011 12:14:37 GMT

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

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Tue, 15 Mar 2011 16:27:26 GMT

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

Re: TJoin and TMap and BigDecimal with a precision specified

_AnonymousUser — Thu, 17 Mar 2011 12:09:20 GMT

Lijo ,
Is there any simplier way than using routines.

Re: TJoin and TMap and BigDecimal with a precision specified

alevy — Thu, 17 Mar 2011 23:16:08 GMT

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.

Re: TJoin and TMap and BigDecimal with a precision specified

Anonymous — Sat, 19 Mar 2011 23:22:53 GMT

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

Re: TJoin and TMap and BigDecimal with a precision specified

jkrfs — Mon, 20 Jun 2011 03:14:45 GMT

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.