Adding a new derived column based on database function

[Anonymous]

Hi All,

I searched quite a lot bit unable to get any information. I have a requirement where within Talend Job I need to add a new column with an built database function

Input is for e.g.

FirstName

LastName

Address

output

FirstName

FirstName_Soundex ....this is a function Soundex(FirstName)

LastName

LastName_Soundex ....this is a function Soundex(LastName)

Address

I am unable to find a component which enables me to do this. TMAP allows adding new column but the syntax is based on Java...it cannot compute db functions.

Many Thanks

Ashish

[Anonymous]

It seems odd that it would interpret it as you say because it seems to be ignoring the quotes and the + (if it assumes the whole value as a string) and doesn't represent what Java would generate if it indeed treats it as Java String manipulation. However, it is clear this is not working for you. Have you tried using a t{DB}Row component to use an bespoke insert statement? It would arguably be slower, but would work.

[Anonymous]

OK, so this one intrigued me. I never like to be beaten so I have tried this out and now have a method that works. First of all I *think* what you have seen *could* be classed as a Talend bug. Everything you said regarding trying this and it failing holds true. However I don't believe that it should. The way I managed to get around this was to do the following.....

1) I added a tMap between my source and target. I used this tMap to create the function/parameter String making use of a literal value. So for example....

"Test.MyFunction('"+row1.myValue+"')"

This creates a String which if you pasted into a SQL developer tool, would return the value you want.
2) I put a tFlowToIterate component and a tFixedFlowInput component after the tMap. This is used to create a globalMap variable for all values in the flow and to convert them back to a flow that is iterated through. It is not quite as quick as it could be, but still pretty speedy. You may not be able to put up with the loss in performance though. The tFixedFlowInput is used to return the values stored globalMap variables. The tFlowToIterate component creates globalMap variables with the name {row}.{column_name}. So for a row called "row1" and a String column called "myValue" feeding into the tFlowToIterate, you would get a globalMap which could be accessed via....

((String)globalMap.get("row1.myValue"))

3) The function/procedure you want to use is added in the way I suggested earlier, but the globalMap is used instead of a String that you build. So in the location where the String was previously  added, you would add (assuming your column used to create the SQL function call is called "myFunction").....

((String)globalMap.get("row1.myFunction"))

When I ran this, I saw the function return a value (and add it to the insert) which represented what had been supplied to the function as a literal value.

I hope this helps.

[Anonymous]

Thanks a lot, struggling a bit on global map variable...is it possible to export the job and share please ?

Many Thanks

[Anonymous]

I'm afraid I do not have my machine with me at the moment. What is it you are stuck with? GlobalMap variables are not too difficult to help out with. Maybe you can post a screenshot and we can help

[Anonymous]

Thanks,

Screen print below:

[cid:image001.png@01D2D56F.4CB8EEC0]


[cid:image002.png@01D2D56F.4CB8EEC0]

Above new column in tmap

Option for tFlowToIterate

[cid:image003.png@01D2D56F.4CB8EEC0]

Schema updated for tFixedFlowInput and added displaysoundex with ((String)globalMap.get("out1.firstnamesoundex"))

[cid:image004.png@01D2D56F.713DB000]
When I run the tlogrow shows

------------------.
| tLogRow_1 |
|=----------------=|
|displaysoundex |
|=----------------=|
|Soundex('Laquita')|
'------------------'

.---------------.
| tLogRow_1 |
|=-------------=|
|displaysoundex |
|=-------------=|
|Soundex('Lura')|
'---------------'

.-----------------.
| tLogRow_1 |
|=---------------=|
|displaysoundex |
|=---------------=|
|Soundex('Yuette')|
'-----------------'
.--------------.
| tLogRow_1 |
|=------------=|
|displaysoundex|
|=------------=|
|Soundex('') |
'--------------'



i.e. all rows...however the output delimited file at the end captures only the last record
displaysoundex

Soundex('')

[Anonymous]

Sorry images did not come through previous message. Please see the attached document.

Thanks

Talend_Soundex_Issue.docx

[Anonymous · Accepted solution]

OK, I see I have been answering the wrong the question. If you want to use your function in a DB input component, you simply use it as you would use it in a normal SQL query. Then assign the function a column name with the "AS" keyword and ensure your schema has a column for it in the right place. The screenshot below shows my example which works....

My function is called "TestFunction" and I provide it with the account.code column. It returns the data as the "name" column. The schema has 4 columns; id, code, name and value. 

Just so that you know, the question I was answering previously was to do with writing to the database and not reading from it. 

[Anonymous]

Many Thanks and you are a star. I agree with you...you never give up and are very helpful indeed.

Thanks

[Anonymous]

 (current job, all the fields are from one SELECT statement)

I was asked to do an enhancement on an existing Talend job. This job is to list all the sales records per item & per UPC.  Currently, all the columns are from a SQL select statement (tDBInput), including a "size" field which calls a database function to get the value. It is ok when we only want to retrieve records for the past week, but it seems no working if I want to get data for the past 2 years. 

How should I re-design the job to make it perform better?

Thanks!

[jp10]

Just faced this issue, where trying to use a SQL function in a tdboutput (like tmssqloutput) component via the additional column.

if you do a "Soundex(?)", it should work. The whole line in Additional column shoud look like this:

"first_name" Varchar(50) "Soundex(?)" Replace first_name

The reason is the generated code is a preparedStatement, and a prepared Statement uses question marks as parameters. I just tried with LEFT(?,2) and it worked beautifully. LEFT being a sql function.

Obviously as an input, it is much easier as you can write any SQL query you like.