CODEWITHSUNDEEP
pentahopentaho-spoon
user11492726
user11492726

Reputation:

pentaho spoon : how to insert value to column conditionnally?

So in my table, I have a column quantity and comment. If the value in quantity is more than 0, then I need to insert a string "available" to column comment , if it equals to 0 then "to order" and finally if it's less than zero, then "warning". What could be the best way?

edited: Guess my question above doesn't show the whole work necessary.

At first, I have a text file where I get fields including quantity.

Then I do some modifications of data (on formula step, I do some calculations on quantity).

In the end I use Table output step to insert them into BD. One of the fields to insert is quantity.

My main question is :

Is it better to insert values to column comment after Table output step (when quantity is already added in BD) using SQL script step?

Upvotes: 0

Views: 1500

Answers (2)

Cyrus
Cyrus

Reputation: 2195

** answer no longer applies with new question details **

If you are updating a database table, by far the best and most efficient solution is to do it in a single SQL statement.

In a Pentaho Job, add a SQL step (under scripting).

In that step enter the SQL command. It will be similar to:

UPDATE MyTable
SET comment = 
    CASE 
        WHEN quantity > 0 THEN 'available'
        WHEN quantity < 0 THEN 'warning'
        ELSE 'to order'
    END
// next line optional, use it if you only need to update some of the records.
WHERE (insert conditions here if you need any)

As an extra comment, it's less than ideal to have two columns that should always be in sync, but depend on an external job to keep them in sync. There are techniques like database triggers or calculating the case/when while retrieving the rows in a select statement that eliminate the chance of having out of sync fields.

Upvotes: 0

nsousa
nsousa

Reputation: 4544

You have basically 3 options:

  1. A filter rows step to split the stream based on the value of quantity, then each of the output streams has an Add constants step to add the new field you want, then combine them again by connecting both Add constants steps to a dummy;

  2. A user defined java expression

  3. A javascript step.

Option 2 is probably the cleanest; option 3 is basically the same as option 2, but with javascript instead of java code; option 1 has the advantage of not requiring any code (though, as the alternative is a one liner, not really an issue). Plus, in option 1 order of rows isn’t necessarily maintained.

Upvotes: 1

Related Questions