Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: PRD 3.5RC1: Query doesnt show fields when I add a parameter

  1. #1
    Join Date
    Mar 2008
    Posts
    364

    Default PRD 3.5RC1: Query doesnt show fields when I add a parameter

    Hi!
    I just started working with parameters in PRD 3.5. Seems like I am still missing something regarding parameters.

    I have a query "q_customers" on an oracle db with the jdbc driver ojdbc14.jar:

    Code:
    select "NAME" from "CUSTOMERS" ...(name is a varchar(20))
    when I rightclick on the query and choose "select query" I get the available field "name" as a child to the query in the treeview which I can drag to the report.

    Then I add a parameter to the query:

    Code:
    select "NAME" from "CUSTOMERS" where "CUSTOMERS"."NAME" = ${namefilter}
    and I define a new parameter "namefilter" which is fed by another query which has the same field (of type varchar(20)).
    Now the "name" dissapears from my available items under "q_customers".
    A "select query" doesnt work anymore.

    I understand that the preview function in the query-editor does not work because it doesnt "know" the parameter. but how can the from the query be accessed from dataset when there are parameters.

    In the prpt examples I can see that the queries still list their resulting fields although there are parameters in them (invoice.prpt or product sales summary.prpt).

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    For parametrized queries, we simply set all the parameter values to <null>, and let the JDBC driver cope with that. Most JDBC drivers happily return a empty resultset, which lists all the column information, but with no rows. As all we want are the columns, that works out nicely for us.

    Do you see any exceptions?
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Mar 2008
    Posts
    364

    Default

    Quote Originally Posted by Taqua View Post
    For parametrized queries, we simply set all the parameter values to <null>, and let the JDBC driver cope with that. Most JDBC drivers happily return a empty resultset, which lists all the column information, but with no rows. As all we want are the columns, that works out nicely for us.

    Do you see any exceptions?
    I only see java.sql.SQLException: Invalid column type when I press the preview button which I guess mean that he interprets the ${param} as the value (btw. couldnt the preview button issue the query with default value of the parameter if it exists? so you'd at least get something).
    When I try "select query" there are no exceptions, it just does nothing.
    So it might "just" be the driver?

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.