Search code examples
sqloracle-databasepentahokettleto-date

Convert all selected columns to_char

I am using oracle SQL queries in an external Program (Pentaho Data Integration (PDI)). I need to convert all columns to string values before I can proceed with using them.

What i am looking for is something that automatically applies the

select to_date(col1), to_date(col2),..., to_date(colN) from example_table;

to all columns, so that you might at best wrap this statement:

select * from example_table;

and all columns are automatically converted.

For explanation: I need this because PDI doesn't seem to work fine when getting uncasted DATE columns. Since I have dynamic queries, I do not know if a DATE column exists and simply want to convert all columns to strings.

EDIT

Since the queries vary and since I have a long list of them as an input, I am looking for a more generic method than just manually writing to_char() infront of every column.

Solution

  • If you are looking for a solution in PDI, you need to create a job (.kjb) where in you take 2 transformations. First .ktr will rebuild the query and the Second .ktr will execute the new query.

    1. First Transformation: Rebuild the query

    enter image description here

    • Read the columns in the Source Table Step (use Table Input step in your case). Write the query select * from example_table; and limit the rows to either 0 or 1. The idea here is not to fetch all the rows but to recreate the query.
    • Use Meta Structure Step to get the meta-structure of the table. It will fetch you the list of columns coming in from the prev. step.
    • In the Modified JavaScript step, use a small snip of code to check if the data type of column is Date and then concat to_Char(column) to the rows.
    • Finally Group and Set the variables into a variable.

    This is the point where the fields are recreated for you automatically. Now the next step is to execute this field with the new query.

    2. Second Transformation: Using this set variable in the next step to get the result. ${NWFIELDNAME} is the variable you have set with the modified column in the above transformation.

    enter image description here

    Hope this helps :)

    I have placed the code for the first ktr in gist here.