Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Parameter for column / table names in SQL query in Pentaho CDE dashboard

  1. #1
    Join Date
    Aug 2017
    Posts
    3

    Default Parameter for column / table names in SQL query in Pentaho CDE dashboard

    Hi,

    I'm trying to use parameters for column and table name to SQL query over JNDY, which doesn't work, while parameters for values work fine.

    I defined 3 simple parameter components with default values:

    Code:
    paramTable = 'dummyTable'
    paramColumn = 'dummyColumn'
    paramValue = 'dummyValue'
    Then I defined an SQL query over JNDI, that looks like:
    Code:
    select * from ${paramTable} where ${paramColumn} = ${paramValue};
    ... which does not work, because parameter values of paramTable and paramColumn don't seem to apply properly, which causes:
    Code:
    Caused by: java.sql.SQLSyntaxErrorException: ORA-00903: invalid table name
    Here are some query modifications, and their results really disappoint me:

    Code:
    select * from dummyTable where dummyColumn = ${paramValue}; 
    -- works fine and delivers 1 row
    
    select * from ${paramTable} where dummyColumn = 'dummyValue'; 
    -- causes 'invalid table name' 
    
    select * from dummyTable where ${paramColumn} = 'dummyValue'; 
    -- does not cause any errors, but if I console.log() the result set of the query, 
       it is empty (1 row is expected though)
    Do I miss something?

    I appreciate any help.

  2. #2
    Join Date
    Aug 2017
    Posts
    3

    Default Parameter for column / table names in SQL query in Pentaho CDE dashboard

    Hi,

    I'm trying to use parameters for column and table name to SQL query over JNDY, which doesn't work, while parameters for values work fine.

    I defined 3 simple parameter components with default values:

    Code:
    paramTable = 'dummyTable'
    paramColumn = 'dummyColumn'
    paramValue = 'dummyValue'
    Then I defined an SQL query over JNDI, that looks like:
    Code:
    select * from ${paramTable} where ${paramColumn} = ${paramValue};
    ... which does not work, because parameter values of paramTable and paramColumn don't seem to apply properly, which causes:
    Code:
    Caused by: java.sql.SQLSyntaxErrorException: ORA-00903: invalid table name
    Here are some query modifications, and their results really disappoint me:

    Code:
    select * from dummyTable where dummyColumn = ${paramValue}; 
    -- works fine and delivers 1 row
    
    select * from ${paramTable} where dummyColumn = 'dummyValue'; 
    -- causes 'invalid table name' 
    
    select * from dummyTable where ${paramColumn} = 'dummyValue'; 
    -- does not cause any errors, but if I console.log() the result set of the query, 
       it is empty (1 row is expected though)
    Do I miss something?

    I appreciate any help.

  3. #3
    Join Date
    Aug 2017
    Posts
    3

    Default Parameter for column / table names in SQL query in Pentaho CDE dashboard

    Hello,

    I'm trying to use parameters for column and table name to SQL query over JNDY, which doesn't work, while parameters for values work fine.

    I defined 3 simple parameter components with default values:

    Code:
    paramTable = 'dummyTable'
    paramColumn = 'dummyColumn'
    paramValue = 'dummyValue'
    Then I defined an SQL query over JNDI, that looks like:

    Code:
    select * from ${paramTable} where ${paramColumn} = ${paramValue};
    ... which does not work, because parameter values of paramTable and paramColumn don't seem to apply properly, which causes:
    Code:
    Caused by: java.sql.SQLSyntaxErrorException: ORA-00903: invalid table name
    Here are some query modifications, and their results really disappoint me:

    Code:
    select * from dummyTable where dummyColumn = ${paramValue}; 
    -- works fine and delivers 1 row
    
    select * from ${paramTable} where dummyColumn = 'dummyValue'; 
    -- causes 'invalid table name' 
    
    select * from dummyTable where ${paramColumn} = 'dummyValue'; 
    -- does not cause any errors, but if I console.log() the result set of the query, 
       it is empty (1 row is expected though)
    Do I miss something?



    I appreciate any help.

Tags for this Thread

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 - 2017 Pentaho Corporation. All Rights Reserved.