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]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

image as per here

[Anonymous]

(first jpg too large ..will try again)

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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