Add two columns together using apache-nifi

Question

I have following scenario. I one database table I have :

| id | name | basic_salary | allowance |
_______________________________________|
| 1  | sach | 2000         | 1000      |
| 2  | nala | 5000         | 2500      |
|______________________________________|

add basic_salary and allowance togther and making it as net_salary and insert to a new table called net_salary

For first step, I used ExecuteSQLRecord processor and can get all the records. But problem is : How to add those two columns coming in flowfile.

So final result should be :

| id | name | net_salary |
|________________________|
| 1  | sach | 3000       |
| 2  | nala | 7500       |

This is directly related to my previous variable question. How to handle flowfile variables to perform operations in apache-nifi?

I used How to compute the sum of multiple columns in PostgreSQL inside ExecuteSQL processor, but it can not understand flowfile variables.

@Lamanus `SELECT COALESCE(basic_salary,0) + COALESCE(allowance,0)1` Error is : **column basic_salary does not exitis** — Sachith Muhandiram, Nov 26 '19 at 09:19
@natarajank **Unable to execute SQL query** , My SQL : `SELECT COALESCE("basic_salary",0) + COALESCE("allowance",0)` — Sachith Muhandiram, Nov 27 '19 at 06:59

score 1 · Accepted Answer · answered Nov 27 '19 at 09:54

There is a tricky one. Use UpdateRecord twice.

The first one is

Record Reader               CSVReader
Record Writer               CSVRecordSetWriter
Replacement Value Strategy  Record Path Value
/net_salary                 concat(/basic_salary, ',', /allowance)

and the second one is

Record Reader               CSVReader
Record Writer               CSVRecordSetWriter
Replacement Value Strategy  Literal Value
/net_salary                 ${field.value:substringBefore(','):toNumber():plus(${field.value:substringAfter(','):toNumber()})}

where it gives the result as follows.

id,name,basic_salary,allowance,net_salary
1,sach,2000,1000,3000
2,nala,5000,2500,7500

Add two columns together using apache-nifi

1 Answers1

Linked