apache-nifi

Update a record using apache nifi


What I am trying to do is exactly same as here

But when I use QueryRecord processor I get an error :

Error while preparing statement.

In my scenario, I have 60+ columns and I want to perform this only on the 59th column.

SQL statement :

select ... (many columns),
case when MSISDN=1994 then 0
when MSISDN=94 then 0
end MSISDN,
..(another a few columns)
from Flowfile

I just want to replace 1994 or 94 with 0.

Do I have to use another processor for this?


Solution

  • As the error message states

    Error while preparing statement.

    your SQL query is invalid.

    Input:

    ID,MSISDN,FOO
    1,1994,X
    2,94,Y
    

    Query:

    SELECT ID,
        CASE 
            WHEN MSISDN=1994 THEN 0
            WHEN MSISDN=94 THEN 1
        END MSISDN,
        CASE 
            WHEN FOO='X' THEN 'BAR'
            WHEN FOO='Y' THEN 'BAZ'
        END FOO
    FROM Flowfile
    

    Output:

    enter image description here

    Flow:

    enter image description here

    QueryRecord properties:

    enter image description here

    PS: if you still can't create a valid query, add the full query to your question, so we can figure out what's wrong.