Passing SQL query as a Parameter in Pentaho?

Question

There is a Pentaho step where I am stuck. This is my SQL query in one of my Pentaho step:

select t.val_1  as Contract, 
       t.val_2  as Service,
       t.val_3  as Calender,
       t.val_4  as MSPProvider,
       t.val_5  as MSPCustomer,
       t.val_6  as ObjectName,
       t.val_7  as EventID,
         to_timestamp(t.val_8,'MM-DD-YYYY HH:MI:SS PM')  as Startdate,
         to_timestamp(t.val_9,'MM-DD-YYYY HH:MI:SS PM') as Startkeydate,
         to_timestamp(t.val_10,'MM-DD-YYYY HH:MI:SS PM') as Enddate,
        to_timestamp(t.val_11,'MM-DD-YYYY HH:MI:SS PM') as Endkeydate,
       t.val_12 as Duration,
       t.val_13 as DurationMod,
       t.val_14 as Suspend,
       t.val_15 as SuspendExternal,
       t.val_16 as Excluded,
       t.val_17 as Target,
       t.val_18 as TargetUnit,
       t.val_19 as Relation,
       t.val_20 as Violated,
       t.val_21 as CalculationType,
       t.val_22 as calculationversion,
       t.val_23 as Category,
       'sec' as ValueUnit,
       t.val_24 as TicketUnit,
       t.val_25 as Severity,
       t.val_26 as Impact,
       t.val_27 as ExclusionID,
       t.val_28 as OriginatorID,
      t.val_25 as SeverityID,
       t.val_29 as ReOpened,
       t.create_date as CreateDate,
       t.modify_date as ModifyDate

from t_slalom_outputs t 

where t.is_active = 1
      and t.table_name = 'TICKET'
    and t.val_4 = '${MSP_PROVIDER}'

      and t.modify_date > (SELECT sp_timestamp FROM public.msp_startingpoints where sp_name = 'etl_export_result_slalom')

The query after modify_date > is selecting data from Postgres Database and the rest of the query is fetching data from Oracle server. How should I divide the two?

One is taking data from the table t_slalom_outputs which is in Oracle and other from Postgres Table msp_startingpoints. The modified date > ( Postgres Query )

Is there any way I can pass the Postgres Query as a parameter value to this whole query?

If I do understand you only care about the rows of ORACLE.t_slalom_outputs with a modify_date greater than a certain value. The limiting value does not depend in any way of the ORACLE data, but you cannot hardcode it because it is taken from a POSTGRES database. — AlainD

AlainD AlainD · Accepted Answer · 2017-10-12T13:04:57

Get the msp_startingpoints from the Postges database with an input table. Then drop the result in a Input table with your query except the t.modify_date > (SELECT ...) has been replaced by t.modify_date > ?, specify the Insert data from step as being the first step.

That's it.

The magic is coming from the fact that the question mark are replaced by the values from the incoming step. In your case you have one column and one row, but you may have more columns (mind the order of the columns) and you more than one row (mind the order of the rows in the result).

Passing SQL query as a Parameter in Pentaho?

1 Answers