topic Re: [resolved] Some Problems with added lookup in Oracle-SF integration in Talend Studio

[resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Sat, 16 Nov 2024 10:49:37 GMT

Talend Version:
Version: 6.1.0.2
Build id:
Hello Community,
I have some problems with my Talend Integration Salesforce Component.
We add some more connections and fields continously to ur Oracle-SF Process in talend.
The next step was an additional query oracle lookup on an Field from an other existing query.
Actually the Oracle-SF Integration looks like this:

The query to Oracle Database from Component "Umsatz Lookup":

"SELECT DISTINCT
(RELFIRMA.FIRMANR),
DATENFELDER_1,
RELES.VORVORJAHR,
RELES.AktJahr,
RELES.VorJahr,
RELES.SATZART
FROM INFOR.RELFIRMA RELFIRMA
LEFT OUTER JOIN INFOR.RELES RELES
ON RELES.DATENFELDER_1 = RELFIRMA.FIRMANR
WHERE SATZART = 'UK'"
Here is the Query from the component "Oracle-Query-account-ktxt":
"SELECT
DISTINCT
(RELFIRMA.FIRMANR),
RELFIRMA.FIRMANR,
RELANSCH.STRASSE,
RELANSCH.ORT,
RELANSCH.LAND,
RELANSCH.STAAT,
RELANSCH.PLZORT,
RELANSCH.POSTFACH,
RELACP.VERTRETER1,
RELANSCH.LANDKNG,
RELADRESSE.ANSCHRIFTNR,
RELFIRMA.ABCKLAS,
RELFIRMA.USTIDNR,
RELFIRMA.KTXT,
RELFIRMA.BEMERKUNG,
RELFIRMA.VERWENDUNG1,
RELFIRMA.CREATEDATE,
RELFIRMA.MODIFYDATE,
RELFIRMA.ABCKLAS,
RELFIRMA.NAME,
RELACP.TEXT0,
RELACP.TEXT1,
RELACP.TEXT2,
RELACP.TEXT3,
RELACP.TEXT4,
RELACP.ZBED,
RELZTLB.KTXT Lieferbedingungen,
RELZTSPE.KTXT \"Spedition/Versand\",
RELZTZB.KTXT Zahlungsbedingungen
FROM (INFOR.RELFIRMA RELFIRMA
INNER JOIN INFOR.RELADRESSE RELADRESSE
ON (RELFIRMA.FIRMANR = RELADRESSE.FIRMANR)
inner JOIN INFOR.RELACP RELACP
ON (RELFIRMA.FIRMANR = RELACP.MNR)
inner join INFOR.RELANSCH RELANSCH
ON (RELADRESSE.ANSCHRIFTNR = RELANSCH.ANSCHRIFTNR))
right outer JOIN INFOR.RELZTLB RELZTLB
ON (RELACP.TEXT0 = RELZTLB.ZTKEY)
right outer JOIN INFOR.RELZTSPE RELZTSPE
ON (RELACP.TEXT1 = RELZTSPE.ZTKEY)
right outer JOIN INFOR.RELZTZB RELZTZB
ON (RELACP.ZBED = RELZTZB.ZTKEY)
WHERE RELFIRMA.VERWENDUNG1 = '1'
AND RELFIRMA.FIRMANR = '32275'
AND RELANSCH.VERWENDUNG1 = '1'
AND RELFIRMA.FIRMANR NOT LIKE 'I%'
AND RELZTSPE.Sprache = 'de'
AND RELZTZB.Sprache = 'de'
AND RELZTLB.Sprache = 'de'"
And here is the Lookup with Mapping :

The problem is, that the mapping still take action in other way as it should be.
The Field DATENFELDER_1, which has an lookup to FIRMANR will be mapped to Umsatz_VorVorjahr__c but it's just declared as an key
an not as an mapping one.
For Testing i've made an query with an doubled Firmanr Field it's called FIRMANR1
Here is the ouput after mapping with the 2 related Fields:

As you can see the 2 Fields has the same Values but this does not reflect the mapping in the prevoius step.
I don't know where these strange behaviour comes in.
Does anyone has an idea
thanks in advance
regards john

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Mon, 15 Feb 2016 10:05:01 GMT

Do i miss sth?
regards fireskyer

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Wed, 17 Feb 2016 11:01:24 GMT

Sorry
..... In my point of view for some inexplicalbe reason no one answers me ???
Why???
I tried to solve my issues for myself and add the answer to the post ....
But in some cases It will be great to get some help from the community
I know that i have no guarentee for support but i don't know why the others got it and i'm not.
regards john

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Wed, 24 Feb 2016 09:29:33 GMT

Hi fireskyer,
Sorry for delay!
Have you checked the outputs from both "Umsatz Lookup" and "Oracle-Query-account-ktxt" to see if the Select query works well?
How did you set the join model? left outer join or inner join in tMap? More information will be helpful for us to locate your issue.
Best regards
Sabrina

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Thu, 25 Feb 2016 09:12:31 GMT

Hello xdishi.

What do you mean with both "Umsatz Lookup" and "Umsatz Lookup".

I you mean the lookup query.

I've tested in my sql program it works correctly with the right data.

On tmap it's an "left outer join".

May i overlook or i interpret some basic mechanism in Talend in an wrong way .. or the Lookup is not implemented properly

Thanks for help

best regards fireskyer

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Thu, 25 Feb 2016 16:18:54 GMT

Moreover here is my Job in Java Code

// ============================================================================
//
// Copyright (c) 2006-2015, Talend Inc.
//
// This source code has been automatically generated by_Talend Open Studio for Data Integration
// / Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
// http://www.apache.org/licenses/LICENSE-2.0
// 
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
package sf_csv.sf_prod_data_migration_q_ktxt_acc_u_0_1;
import routines.Mathematical;

To see the whole post, download it here
OriginalPost.pdf

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Thu, 03 Mar 2016 12:23:38 GMT

Hello @xdishi,

Have you checked the outputs from both "Umsatz Lookup" and "Umsatz Lookup" to see if the Select query works well?

1. Output is checked with sql developer Result: All ok all Colums are righted Assigned to the Data
2. Output checked with the query from the lookup in Talend and out put to an Excel File Result: All ok all Colums are righted Assigned to the Data

How did you set the join model? left outer join or inner join in tMap? More information will be helpful for us to locate your issue

Is use for the lookup an left outer join

More information will be helpful for us to locate your issue

which more infos do you need
regards john

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Fri, 04 Mar 2016 09:18:17 GMT

Hi,
We don't find any error in your job design.

1. Output is checked with sql developer Result: All ok all Colums are righted Assigned to the Data
2. Output checked with the query from the lookup in Talend and out put to an Excel File Result: All ok all Colums are righted Assigned to the Data

The lookup "left out join" doesn't work well for your case?
Could you please simply give us your expected result and actual result?
Best regards
Sabrina

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Fri, 04 Mar 2016 09:39:01 GMT

As you can see the 2 Fields has the same Values but this does not reflect the mapping in the previous step.

If you look at your data one value has a decimal point while the other doesn't so they won't match.
32275.0 and 32275

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Wed, 09 Mar 2016 11:01:57 GMT

Hello Guys
@janhess2

If you look at your data one value has a decimal point while the other doesn't so they won't match.
32275.0 and 32275

It doesn't matter cause this Field is the output from Salesforce and has nothing to do with the Mapping.

The lookup "left out join" doesn't work well for your case?
Could you please simply give us your expected result and actual result?
Best regards
Sabrina

Sure no problem
1 SQL statement:

SELECT DISTINCT RELES.AKTJAHR,
                RELES.VORJAHR,
                RELFIRMA.NAME,
                RELFIRMA.KTXT,
                RELES.VORVORJAHR,
                RELFIRMA.FIRMANR,
                RELFIRMA.ABCKLAS, 
                RELANSCH.STRASSE,
                RELANSCH.ORT,
                RELANSCH.LAND,
                RELANSCH.STAAT,
                RELANSCH.PLZORT,
                RELANSCH.PLZPOSTFACH,
                RELANSCH.POSTFACH,
                RELACP.VERTRETER2,
                RELACP.VERTRETER1,
                RELANSCH.LANDKNG
  FROM (((INFOR.RELFIRMA RELFIRMA
          INNER JOIN INFOR.RELADRESSE RELADRESSE
             ON (RELFIRMA.FIRMANR = RELADRESSE.FIRMANR))
         INNER JOIN INFOR.RELANSCH RELANSCH
            ON (RELADRESSE.ANSCHRIFTNR = RELANSCH.ANSCHRIFTNR))
       -- INNER JOIN INFOR.RELES RELES
        LEFT OUTER JOIN INFOR.RELES RELES
           ON (RELES.DATENFELDER_1 = RELFIRMA.FIRMANR))
       LEFT OUTER JOIN INFOR.RELACP RELACP ON (RELFIRMA.FIRMANR = RELACP.MNR)
 WHERE     RELANSCH.VERWENDUNG1 = 1
        AND     RELES.SATZART = 'UK' 
        AND RELFIRMA.VERWENDUNG1 = 1
        AND RELFIRMA.FIRMANR = '18025'

This is the Line which is Similar to the Lookup from Talend side :

LEFT OUTER JOIN INFOR.RELES RELES
ON (RELES.DATENFELDER_1 = RELFIRMA.FIRMANR))

The Result is :

AKTJAHR"," VORJAHR","NAME","KTXT"," VORVORJAHR"," FIRMANR","ABCKLAS","STRASSE","ORT","LAND","STAAT","PLZORT","PLZPOSTFACH","POSTFACH","VERTRETER2","VERTRETER1","LANDKNG"
140, 919,"AcmeGmbH","Acme GmbH", 2470," 18025","A","Dree 5","Berlin","","Deutschland","26405","","","","14","D"

Note: The Record is slightly edited with Fake Data but i will edit the other records also that is clear to see whats going wrong.
This is the the Record which is correct and should be so:
Short Form ( Fields which are relevant):
AKTJAHR: 140
VORJAHR: 919
VORVORJAHR: 2470
Firmennummer: 18025

2. The Excel Output ( converted in csv) from Talend:

VORVORJAHR; VORJAHR; AKTJAHR;ORT;PLZORT;Staat;STRASSE;KTXT;Name;VERTRETER1;Verwendung__c; FIRMANR

18025; 140; 2470;Berlin;26405;Deutschland;Dree 5;AcmeGmbH;Acme GmbH;14;1; 18025

As you can see the Fields : VORVORJAHR are the value of Firmanummer, VORJAHR should be the value 919, AKTJAHR should be the
value of VORJAHR
So sth. is wrong.
Short Form ( which Fields are Relevant):
VORVORJAHR : 1825|| Right Value: 2470
VORJAHR: 140 || Right Value: 919
AKTJAHR: 2470 || Right Value: 140

Firmanummer: 18025

3. Output from Salesforce :
Id, AKTJAHR,BillingAddress.latitude,BillingAddress.longitude,BillingAddress.city,BillingAddress.country,BillingAddress.countryCode,BillingAddress.postalCode,BillingAddress.state,BillingAddress.stateCode,BillingAddress.street,BillingCity,BillingCountry,BillingLatitude,BillingLongitude,BillingPostalCode,BillingState,BillingStreet,KTXT__c,Name, VORVORJAHR, VORJAHR, FIRMANR
0012400000EI30UAAT, 2470,"","",Berlin,"","",26405,Deutschland,"",Dree 5,Berlin,"","","",26405,Deutschland,Dree 5,ACME GmbH,ACME GmbH, 18025, 140, 18025
In short form ( it's the same output as excel):
AKTJAHR: 2470 || Right Value: 140
VORVORJAHR : 1825|| Right Value: 2470
VORJAHR: 140 || Right Value: 919
Firmanummer: 18025

As you can see the Output from Talend in Salesforce and in Excel are the Same.
So i think i can exclude an error on Salesforce Side.
regards john

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Tue, 15 Mar 2016 15:26:33 GMT

So some notes or suggestions for me.
Or do you need some more infos or explainations.... is sth. unclear
best regards fireskyer

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Thu, 31 Mar 2016 14:20:30 GMT

Bumping .....
regards fireskyer

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Fri, 08 Apr 2016 08:20:36 GMT

No one again ?
regards

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Wed, 13 Apr 2016 08:57:06 GMT

Update:
Whats is crucial
If i change the Order from the Fields in the Mapping the output is null for the affected fields.
regards john

Re: [resolved] Some Problems with added lookup in Oracle-SF integration

Anonymous — Thu, 14 Apr 2016 22:23:38 GMT

Update :
I had an other job and i could solve the problem with an very idiotical trick which gives no sense.
I rearaange the Fiels from the tmap schema a little bit ( put the field down in the schema ) ... but with other job it doesnt work.
But back to this actual job:
I haven't achieved to make this job working.
What iv'e done is to rebuild the job from scratch in a completely different way.
@xdishi:
Do you have researched the java code from the job ? Or only the job conception
I don't know why this job doesn't work properly.
But I asssume that Talend cannot process some Jobs with Lookups within an join statement together not really properly.
But depsite of that guess ... i just change everything with other components.
so in any way the issue is "solved " .. but I'M really interested in the fact what was wrong with the job
regards john