Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: How to pass conditioned date parameters to Table input step?

  1. #1
    Join Date
    Feb 2017
    Posts
    2

    Default How to pass conditioned date parameters to Table input step?

    Hi people, first of all I'm new here and if this question is in the wrong area I beg you to point me what I need to do. So, let me tell you my problem:

    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:

    1) created both parameters based on a Modified Java Script Value step

    Name:  set_param_ktr.JPG
Views: 697
Size:  12.9 KB

    2) passed them to Set Variables, all inside the first transformation.

    3) on the second to the last transformations, I need to use these parameters in the SELECTs.

    Name:  transf2_ktr.JPG
Views: 712
Size:  14.1 KB

    --
    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.

    Name:  job_example.JPG
Views: 679
Size:  17.6 KB

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

    The SELECT I'm trying to (look to the data_atendimento BETWEEN 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;
    Last edited by Nior; 02-01-2017 at 03:11 PM.

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Why would you use Kettle variables for this?

    Edit your database connection and add your SQL code to do some calculation, like:

    Code:
    -- H2 SQL
    SET @YESTERDAY = DATEADD('DAY', -1, CURRENT_DATE);
    SET @FIRSTDAY = DATEADD('DAY', 1 - DAY_OF_MONTH(@YESTERDAY), @YESTERDAY);
    Now you can refer to SQL variables in your SELECT statement:

    Code:
    ... AND data_atendimento BETWEEN @FIRSTDAY AND @YESTERDAY ...
    I could be wrong, though
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Feb 2017
    Posts
    2

    Default

    Thank you for the answer. I tried to use Kettle variables because its a ongoing project and I wanted to control all variables (these wont be the only ones, I want to have conditions to generate log files in dynamic paths and name as well) in a single place instead of set them for every query.

    If I do not find any solution to this, of course will try to do it inside the SQL codes as you sugest. Thank you again.

    EDIT: finally found my solution: I just needed to use the Javascript step from the job to pass the parent_job.setVariable parameter to the rest of the transformations.
    Last edited by Nior; 02-02-2017 at 12:44 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.