[resolved] Some Problems with added lookup in Orac... - Qlik Community

Anonymous · ‎2016-02-03

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

Anonymous · ‎2016-02-15

Do i miss sth?
regards fireskyer

Anonymous · ‎2016-02-17

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

Anonymous · ‎2016-02-24

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

Anonymous · ‎2016-02-25

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

Anonymous · ‎2016-02-25

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

Anonymous · ‎2016-03-03

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

Anonymous · ‎2016-03-04

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

Anonymous · ‎2016-03-04

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

Anonymous · ‎2016-03-09

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

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

ORACLE

SQL

Talend Data Integration

v6.x