Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: LIKE clause in a Select, what is the correct syntax?

  1. #1
    Join Date
    May 2012
    Posts
    13

    Default LIKE clause in a Select, what is the correct syntax?

    I'm sure it is easy but I was not able to find the way!

    I have a first Text box parameter called prmString and I have a second drop down parameter with the following SELECT :

    SELECT tblCode from MyTable WHERE tblCode LIKE {prmString}

    How have I to add the % needed? I tried to put it in the Post Processing Formula of the prmSting parameter:

    =[cadena]&"%" : do nothing
    ={cadena}&"%" : syntax error

    And what about the ' ' ???


    I'm sure it is easy but I was not able to find the way!

    I already was looking for this matter in the Forum... like this

    Your problem lies in your query ...
    cola like concat(${param_1}, '%')
    will end up being
    cola like "value"%
    Use a post processing formula to add the % to the parameter value so that its correctly passed to the query. change your query to
    cola like ${param_1}

    You can define the post processing formula where you set up the parameter initially. Good luck!


    Thanks in advance!

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

    Default

    First and foremost: All parameter in SQL datasources must be written as ${parameterName}. The syntax without the dollar will not be accepted by the reporting engine.

    As said in your quoted text:

    Either use the "CONCAT" SQL function to add the "%" to your parameter via your SQL query.
    That is the easiest solution.

    or

    declare a hidden parameter (name it "param_1a", for instance) after your parameter declaration for "param_1". Note that you can reorder parmater in the tree via the "move forward/backward" option in the context menu. In that hidden parameter use the post-processing formula

    =[param_1] & "%"

    and in your SQL query then use the "param_1a" as your value for the like condition.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    May 2012
    Posts
    13

    Default

    Thanks for your answer!

    But I'm still confused. If I put the "%" in the Select clause I get an error, I tried different expressions (concat, concatenate, with ', with ",...)

    So I followed your instructions about the hidden parameter. I get no errors, but the Select clause with the LIKE condition is always empty.

    What I'm doing wrong?

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 - 2019 Hitachi Vantara Corporation. All Rights Reserved.