Solved: Adding a new derived column based on database func... - Qlik Community

Anonymous · ‎2017-05-23

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 · ‎2017-05-25

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.

View solution in original post

Anonymous · ‎2017-05-23

Take a look at step 8 of this tutorial (https://www.rilhia.com/tutorials/using-auto-generated-primary-key-update-row-just-inserted-mysql-dat...). I think it *may* be what you need.

TRF · ‎2017-05-23

I'm afraid what you want to do is impossible.

If you want to add a column associated to a database function, you have to call this function using the "t<db>SP" component and put the result into the desired field.

TRF · ‎2017-05-23

Arrrgh! @rhall, experts do not agree with each other

cterenzi · ‎2017-05-23

@rhall wrote:

Take a look at step 8 of this tutorial (https://www.rilhia.com/tutorials/using-auto-generated-primary-key-update-row-just-inserted-mysql-dat...). I think it *may* be what you need.

Thank you, this was a very helpful example.

Anonymous · ‎2017-05-24

Thanks for the detailed instructions. I am afraid but does not work fully.

tMSSqlOutput component has been modified.

Advanced Settings -> Additional Columns -> + -->

SQL Expression if I use "Soundex(first_name)"

first_name is my column

Invalid column name 'first_name'.
[ERROR]:

tMSSqlOutput_1 - Invalid column name 'first_name'.

if I add instead a standard SQL Server function "DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1)"

this works fine

I have tried all options

"Soundex([first_name])"

"Soundex(schema.table_name.first_name)"

"Soundex(table_name.first_name)"

Unfortunately none work...how can I reference the column in the function?

Thanks

Anonymous · ‎2017-05-24

Can you try something like "Soundex("+input_row.first_name+")" ?

And also remember to mention it in the reference column.

Anonymous · ‎2017-05-24

Thanks

I had tried that but does not work. Reference column is used for position i.e. before/after or replace.

"Soundex("+ row1.first_name +")"

returns NULL for all columns as its not able to interpret it as column name

So sorry not able to achieve the end result.

Anonymous · ‎2017-05-24

A subtle difference, but have you tried....

"Soundex('"+ row1.first_name +"')"

I'm adding a ' before the double quote and after the double quote. This is done so that the value of row1.first_name (lets assume it is Richard) is added as a literal value at runtime. So the actual method passed to SQL would be...

Soundex('Richard')

That *might* work

Anonymous · ‎2017-05-24

Thanks

Had tried that, but it interprets "Soundex('"+ row1.first_name +"')"

As "Soundex('row1.first_name')"
row1.first_name is a string and it returns N400 for all rows.

Adding a new derived column based on database function

Java

Talend Data Integration

v6.x