Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Dynamic schema on querys using parameters

  1. #1
    Join Date
    May 2017
    Posts
    4

    Default Dynamic schema on querys using parameters

    Hello,

    I need to build dynamic querys where the schema will be passed by parameter.

    I'm using a parameter String, named schema.

    My query is similar to this:
    Code:
    SELET
    *
    FROM
    ${schema}.user.table
    The result is:
    Code:
    SELET
    *
    FROM
    'schema1'.user.table
    This is not working, because of the '' around the schema name, and I need the query to be:
    Code:
    SELET
    *
    FROM
    schema1.user.table
    How can I remove '' from schema name, or any other way to to dynamic querys do different schema using parameters?

  2. #2
    Join Date
    Aug 2016
    Posts
    142

    Default

    You can't use parameters directly to edit the query structure as it is basically sql injection.

    You can however use query scripting to do so.

    Open your query and select the query scripting tab. Select Groovy as the language

    Something like this should then do it, assuming your parameter is set to string type and has a default value set. Paste the below in

    def computeQuery (query, queryName, dataRow)
    {
    query = query.replaceAll("schema_filter",dataRow.get("schema"))
    return query;
    }

    "schema" in the above is the name of your parameter. Then the part of your query that you want parameterised should look like this:

    schema_filter.user.table

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.