tMap expression syntax

Five Stars

tMap expression syntax

 

   Hello!! 

 

   Sameon can help us?

 

We need to join to tables by a query in the tmap, we know how to do this instrucction in sql, we try to do it in JAVA in the Expression Builder, but we can get the good resoult, the SQL query is this:

select a.hotel_name, a.city_code, a.city_name, b.name, b.location_city_code, b.location_name,a.longitud, a.latitud, b.longitude_new, b.latitude_new, b.expedient_number from Veci_marketing_v.mdm_hotel as a, 
veci_stg_did.hotel_norm_city as b 
where (a.longitud between b.longitude_new - 0.3 and b.longitude_new + 0.3) and (a.latitud between b.latitude_new -0.3 and b.latitude_new +0.3) and b.matching_distances is null order by b.name asc

The condiction that we have to include in the Tmap to extract the correct data for every lines from the first table it would be like the SQL WHERE.


Accepted Solutions
Employee

Re: tMap expression syntax

Hi,

      

      Theoretically, you can do it by joining both tables using tmap as shown below.

image.png

I have created the expression as below considering the latitude and longitude values are always not null for a row.

 

Relational.isNull(matching_distances) &&
( a.longitud >= (b.longitude_new - 0.3)
&& a.longitud <= (b.longitude_new + 0.3)
&& a.latitud >= (b.latitude_new - 0.3)
&& a.latitud <= (b.latitude_new + 0.3) 

 

 

     After tmap, you add a tsortrow component to do the order by part of query.

 

    However, we need to assess the data volumes in underlying MDM and stage tables here. If we can filter the data source at the source, always do it to reduce the result size for next phase. So in your specific scenario, it will be a good idea to do the filtering at tDBinput component level and then do further processing.

 

    There are other methodologies like generating the where clause in tDBInput component in dynamic way so that same results are achieved. But it seems in your case, the where case is static. So do the filtering at source and then proceed to get maximum performance.

 

Warm Regards,

 

Nikhil Thampi

 


All Replies
Employee

Re: tMap expression syntax

Hi,

      

      Theoretically, you can do it by joining both tables using tmap as shown below.

image.png

I have created the expression as below considering the latitude and longitude values are always not null for a row.

 

Relational.isNull(matching_distances) &&
( a.longitud >= (b.longitude_new - 0.3)
&& a.longitud <= (b.longitude_new + 0.3)
&& a.latitud >= (b.latitude_new - 0.3)
&& a.latitud <= (b.latitude_new + 0.3) 

 

 

     After tmap, you add a tsortrow component to do the order by part of query.

 

    However, we need to assess the data volumes in underlying MDM and stage tables here. If we can filter the data source at the source, always do it to reduce the result size for next phase. So in your specific scenario, it will be a good idea to do the filtering at tDBinput component level and then do further processing.

 

    There are other methodologies like generating the where clause in tDBInput component in dynamic way so that same results are achieved. But it seems in your case, the where case is static. So do the filtering at source and then proceed to get maximum performance.

 

Warm Regards,

 

Nikhil Thampi

 

Five Stars

Re: tMap expression syntax

 

   Thanks Nikhil Thampi.

 

    We just had to adapt to the fields we had, we will validate the results but it seems to work ok.

 

  Thank you very much!

    

Employee

Re: tMap expression syntax

Hi,

 

     Happy to hear that the solution helped you.

 

     Enjoy Talend :-)

 

Warm Regards,

 

Nikhil Thampi