How to pass conditioned date parameters to Table input step?

Question

I need to base my SQL SELECTs in several transformations in a job on date parameters and I'm having problems to make it work.

The plan is: to have a begin_date and a end_date parametrized on the following condition: if it's 1st of any month, the parameters will become the first and the last day of the last month; if not, its the first day of the current month until yesterday.

What I already did: created both parameters based on a Modified Java Script Value step and passed them to Set Variables, all inside the first transformation.

On the second to the last transformations, I need to use these parameters in the SELECTs.

What I tried and didn't work:

1) use the Get Variables to get both (they appear in the dropdown list, from where a get them), created a hop to the Table Input and changed the dates in the SQL code to ${fst} and ${lst}. Tried with and without check 'Replace variables in script?'

2) not use the Get Variables but wrote them in the Parameters tab at transformation properties. Also tried with and without check 'Replace variables in script?'

3) check 'Copy previous results to parameters?' and 'Execute for every input now?' in the Advanced tab of Job entry details of the second transformation. In the case it flags success at execute, but don't execute the following transformations.

Guess I may be missing something simple, but couldn't get the answer by searching.

EDIT: Some screens with explanation

1) my first transformation which is to set variables

2) my second transformation which i try to get and use the variables

3) the query i'm trying to do (look at the BETWEEN ${fst} AND ${lst} part)

WITH cte AS (
SELECT MAX(data_atendimento) AS data_atendimento, MAX(a.id_customer) AS id_customer, MAX(id_atendimento) AS id_atendimento, MAX(operador) AS operador
FROM tb_atendimento a INNER JOIN tb_param_atd p ON a.id_atendimento = p.id_atd
WHERE id_especializacao = 26 
    AND h_data -> 'CPC_OI' IS NULL 
    AND data_atendimento BETWEEN ${FST} AND ${LST} --DATE_TRUNC('month', CURRENT_DATE) AND CURRENT_DATE + INTERVAL '1 DAY' 
GROUP BY CAST(a.id_customer AS VARCHAR) || CAST(data_atendimento AS VARCHAR))
SELECT *, CASE WHEN MAX(data_atendimento) OVER (PARTITION BY id_customer) = data_atendimento THEN true ELSE false END AS ultimo_atendimento FROM cte;

Always a good idea to place the same question in [more than one place](http://forums.pentaho.com/showthread.php?218543). — marabu, Feb 02 '17 at 07:22

jacktrade · Accepted Answer · 2017-02-01T18:59:50.093

2

try using placeholders (?) instead replace variables.

look at this screenshot from one of my kettle transformations (similar to what you are trying to achieve). The variable "timenow" comes from a previous js modified step, same as yours.

If you are going to use more than one parameter, keep in mind will be binded in order of appareance from the down box "Parameters:" (shown in the screenshot also)

edited Feb 01 '17 at 18:59

answered Feb 01 '17 at 18:45

jacktrade

3,125
2
36
50

Thank you for your answer, let me ask a thing: are you doing a hop direct from the JS modified to your SQL Script and it gets the variables or are you putting the variables in a first transformation to use it in others? – Daniel Souza Feb 01 '17 at 19:16
yes, the previous step from this , is a js modified script step, click on get fields and choose your params (in order) to be used by your query – jacktrade Feb 01 '17 at 19:17
so you have to replace this: "BETWEEN ${fst} AND ${lst}" with BETWEEN ? AND ? – jacktrade Feb 01 '17 at 19:25
thank you again, I edited the post to maybe clarify more over the question, and , besides my preference is to set variables only one time (because there will be plenty more of them by the end of the project) if I don't find any solution to this I will be using yours – Daniel Souza Feb 01 '17 at 19:26

How to pass conditioned date parameters to Table input step?

1 Answers1