Use of a tmap field in a query for tMsSqlInput

Seven Stars

Use of a tmap field in a query for tMsSqlInput

Hi,

I would use a field that exist in tmap to use it with query in tMsSqlInput like this 

"select * from Table
where name="+row1.name

But when i run the job I get this error

 

c12.PNG

Can you help me please ?

Thanks in advance.

 

Accepted Solutions
Fifteen Stars TRF
Fifteen Stars

Re: Use of a tmap field in a query for tMsSqlInput

It depends.

If most of the rows from main flow are linked with just few rows from the lookup, it should be efficient to use "Reload at each time (cache)" option to get only the required rows from database. Of course, columns used as criteria are supposed to be indexed on database side.

Else, if each row from main flow is linked with a different rows from the lookup you'll have to load all the rows from the lookup table, so "Load once" is the option in such a case.


TRF

All Replies
Fifteen Stars TRF
Fifteen Stars

Re: Use of a tmap field in a query for tMsSqlInput

Hi,
First, because the name column is a string, the exact syntax should be:
"select * from Table
where name='"+row1.name+"'"
Is it enough to solve your problem? Not sure but you have to share your job design for a better answer.

TRF
Seven Stars

Re: Use of a tmap field in a query for tMsSqlInput

Thank you for your replay but it doesn't solve the problem.

e1.PNGe2.PNG

Fifteen Stars TRF
Fifteen Stars

Re: Use of a tmap field in a query for tMsSqlInput

Now you have a new problem, it's a compilation error.

Click on "Code" tab and go to line 4103 to get the exact compilation error.


TRF
Fifteen Stars TRF
Fifteen Stars

Re: Use of a tmap field in a query for tMsSqlInput

Also share tMap_3


TRF
Seven Stars

Re: Use of a tmap field in a query for tMsSqlInput

e3.PNGe5.PNGe4.PNG

With error S connot be resolved to a variable ! 

Fifteen Stars TRF
Fifteen Stars

Re: Use of a tmap field in a query for tMsSqlInput

It seems the error is not there. Move the mouse pointer on the little red rectangle in the right marging, a popup should give you the message


TRF
Seven Stars

Re: Use of a tmap field in a query for tMsSqlInput

Capture d’écran (1).png

Fifteen Stars TRF
Fifteen Stars

Re: Use of a tmap field in a query for tMsSqlInput

OK, you cannot use S.name in the SQL query as it not a known field in this component.

Do you use "Reload each time" as the Lookup Model in tMap_3?

This is the way to do what you expect (I think so).

Refer to this document for details.


TRF
Seven Stars

Re: Use of a tmap field in a query for tMsSqlInput

In fact, my need is to compare the contents of each incoming file by the lines in the database through the name to lighten the memory...

Fifteen Stars TRF
Fifteen Stars

Re: Use of a tmap field in a query for tMsSqlInput

So, 2 options:

1- follow the link from the previous response

2- get the lines into a tHashOutput the reuse this content for the join as you did in the tMap

Except if you have millions records, it should works.

Based on your job, here is what it should looks like (errors are normal, components are not updated):

Capture.PNG

 


TRF
Seven Stars

Re: Use of a tmap field in a query for tMsSqlInput

I have millions of lines so I will follow the first soltion and I will keep you informed.

Thanks a lot Smiley Happy

Seven Stars

Re: Use of a tmap field in a query for tMsSqlInput

Thanks for your help this solves my problem.

But just a question,the manner  of browse the table and do the research line by line will take a lot of time not?
If I use to load once it must be faster yes/no ?

Fifteen Stars TRF
Fifteen Stars

Re: Use of a tmap field in a query for tMsSqlInput

It depends.

If most of the rows from main flow are linked with just few rows from the lookup, it should be efficient to use "Reload at each time (cache)" option to get only the required rows from database. Of course, columns used as criteria are supposed to be indexed on database side.

Else, if each row from main flow is linked with a different rows from the lookup you'll have to load all the rows from the lookup table, so "Load once" is the option in such a case.


TRF
Seven Stars

Re: Use of a tmap field in a query for tMsSqlInput

Thanks a lot Smiley Happy 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now