Best Way to apply SQL logic to a txt file

[rwnetwork]

Hello, 

I have joined 2 .txt files in talend using tmap into new .txt file.  I would now like to apply very specific logic as well to this new .txt file and the current logic is in SQL.  (My goal here is to use Talend instead of SSIS).  See job snapshot below.

https://www.screencast.com/t/VwDsPsTY7Z7A

Anyone have suggestions on the best way to apply this logic?  Looking to what component(s) I should use.

Thanks!

concat(A,B) = (select concat(B,C) )

from (select D, MAX(H)) DATE 

from (SELECT U,V)

FROM xyz

WHERE

S= A and

F= G AND

V <= R

) X

[Anonymous]

I'm afraid your pseudocode does not make any sense. To start with, your brackets do not match up. Can you give us an example with data of what you want to achieve. You can do this sort of stuff, but I can't figure out exactly what it is you want to do.

[rwnetwork]

Hi there, 

Thanks for responding.  I was trying to insert letters as the alias field name and table names to avoid uploading private data, but I guess I made it more confusing.

The sql code shouldn't be as of concerning to my question.  

What I'm struggling with is how to take 1 large .txt files that I have created and then insert a sql query back to another .txt file.

So this 1st link below shows that I joined 2 text files that created the 1 large text file

https://www.screencast.com/t/lFmXPd2n5

Now I want to take that large text file and apply sql logic (as it's already done) BACK to the acctjoin file that is in the 1st snapshot.

Maybe I'm going about this wrong?

I'm trying not to use Java expression builder as I think it will take me longer to learn that than use a sql query.

Did I confuse you more or ................

HA

Please advise.  Thank you sir  🙂

[Anonymous]

I see. I would highly recommend you learn to use Java if using Talend. It really does help with problems like this and is far more flexible than SQL when manipulating data. However, I understand that it can be quite daunting to learn a new language. Another way to get around this problem of yours is to write your data to a database and then read the data back in with your required SQL. 

[rwnetwork]

Ok yes, I plan to learn more java as it looks I seem to always have advanced logic that will require it in my jobs.

I did, however, attempt to create a table on our AS400, but the file was too large to create.  I used the tcreatetable component.

Is tcreatetable the component you would use to create databases?

Thanks again for your help.

Any suggestions help me at this point.............Our SSIS package is crashing and taking way too long and I think Talend could do this faster and more efficiently.

[Anonymous]

How big is the file? You can create a table using the tAS400Output component to create a table. Look at the "Action on Table" option. You can select "Create table if does not exist" to create the table if it does not exist.

[rwnetwork]

The file has 1.4 Billion rows and is 132,338,514 KB.

I will try your other suggestion here and see if it will work from that component.

Thanks!

I'll advise.