Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Worng/mixed parameter values on PRD 6.0

  1. #1
    Join Date
    May 2017
    Posts
    4

    Default Wrong/mixed parameter values on PRD 6.0

    I have a report that is using 10 parameters (2 main ones, and 8 are calculated) and I'm having a problem since the parameters are "mixing" the values.

    The parameters are (by this order):
    • DATEFROM
    • dateFromFormated (depends on DATEFROM)
    • dateToFormated (depends on DATEFROM)
    • Schema
    • Schema_db1 (depends on Schema)
    • Schema_db2 (depends on Schema)
    • Schema_db3 (depends on Schema)
    • hourDiff (depends on Shema_db1)
    • convertedFrom (depends on dateFromFormated and hourDiff)
    • convertedTo (depends on dateFromFormated and hourDiff)


    To check the values, I have commented code on the query:
    /*
    ${DATEFROM} as DATEFROM
    ${dateFromFormated} as dateFromFormated
    ${dateToFormated} as dateToFormated
    ${Schema} as Schema
    ${Schema_db1} as Schema_db1
    ${Schema_db2} as Schema_db2
    ${Schema_db3} as Schema_db3
    ${hourDiff} as hourDiff
    ${convertedFrom} as convertedFrom
    ${convertedTo} as convertedTo
    */

    When I run the report, with the following values:
    DATEFROM = 2018-01-15
    Schema = test
    hourDiff = 3600000

    The report returns the values on the parameters
    /*
    1514764800000 as DATEFROM
    1514851200000 as dateFromFormated
    'test' as dateToFormated
    'test.db1' as Schema
    'test.db2' as Schema_db1
    'test.db3' as Schema_db2
    3600000 as Schema_db3
    0 as hourDiff
    0 as convertedFrom
    'test.db3' as convertedTo
    */

    Any clue on what I am doing wrong?
    Last edited by Costa; 01-15-2018 at 11:10 AM.

  2. #2
    Join Date
    Aug 2016
    Posts
    142

    Default

    You shouldn't need another parameter to format a date. Just enter your format into the "Data Format" section of the parameter.

    Based on the names of your parameters I don't think it will work anyway. Are you trying to do something like

    SELECT * FROM ${Schema_db1}.table

    If so, it will not work as intended as you cannot substitute structural elements of a query with a parameter. You need to use query scripting for that

    As for the issues with the commented fields. I think the issue there might be due to the comment. I think it depends on your DB but for me if I have a parameter in a comment, the query fails. I can't remember the error but it would only run if the parameter was removed from the comment

    Perhaps your DB is treating this differently and putting in the wrong values?

  3. #3
    Join Date
    May 2017
    Posts
    4

    Default

    Quote Originally Posted by Deanicus View Post
    You shouldn't need another parameter to format a date. Just enter your format into the "Data Format" section of the parameter.

    Based on the names of your parameters I don't think it will work anyway. Are you trying to do something like

    SELECT * FROM ${Schema_db1}.table

    If so, it will not work as intended as you cannot substitute structural elements of a query with a parameter. You need to use query scripting for that

    As for the issues with the commented fields. I think the issue there might be due to the comment. I think it depends on your DB but for me if I have a parameter in a comment, the query fails. I can't remember the error but it would only run if the parameter was removed from the comment

    Perhaps your DB is treating this differently and putting in the wrong values?
    Hi Deanicus,

    I'm using the dateFromFormated because I need the value in UTC, and DATEFROM is from a date picker.

    The SELECT * FROM ${Schema_db1}.table is working perfectely, but has to be SELECT * FROM "${Schema_db1}".table. I have that in a lot of other reports, this one is the only that is causing this trouble, because is the one with a lot of other parameters.

    I'll check that comment issue, never realized it could be it.

    Thanks!

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.