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

 
1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars TRF
Twelve 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
14 REPLIES
Twelve Stars TRF
Twelve 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
Six 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

Twelve Stars TRF
Twelve 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
Twelve Stars TRF
Twelve Stars

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

Also share tMap_3


TRF
Six 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 ! 

Twelve Stars TRF
Twelve 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
Six Stars

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

Capture d’écran (1).png

Twelve Stars TRF
Twelve 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
Six 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...

Twelve Stars TRF
Twelve 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
Six 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

Six 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 ?

Twelve Stars TRF
Twelve 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
Six Stars

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

Thanks a lot Smiley Happy