Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: xaction syntax to sql like '%xx%' pattern matching

  1. #1
    Join Date
    May 2009
    Posts
    5

    Default xaction syntax to sql like '%xx%' pattern matching

    Hi,
    I'm using Report Design and BI server stable 2.0.0. I need a report with parameterized query using sql LIKE parameter, as for example:

    select myField1 from myTable where myField2 like '%test%' and myYear=2009


    I tried
    select myField1 from myTable where myField2 like '%${MYSTRING}%' and myYear='${MYYEAR}'

    but although the publishing worked well and User Console opened html page correctly, when I filled in MYSTRING parameter (string "test") an exception ocurred:

    Error: SQLBaseComponent.ERROR_0006 - [pt_122] Could not execute report SISPAC 2.xaction - java.sql.SQLException: Invalid parameter index 3. (org.pentaho.platform.plugin.action.sql.SQLLookupRule)

    The single quotes of LIKE are being misinterpreted by xAction parser.

    If I change the query to
    select myField1 from myTable where myField2 like '${MYSTRING}' and myYear='${MYYEAR}' and fill in HTML field MYSTRING (after rendered by User Console) with %test% and it worked like expected.

    What should be the correct syntax to encapsulate the '%%' pattern matching inside xAction?

  2. #2
    Join Date
    May 2009
    Posts
    5

    Thumbs up Solved

    A friend of mine used Eclipse Report Studio with the same query and it mounted xAction as
    ... like '%'+{PREPARE:MYPARAMETER}+'%' ...

    so based on the assembled XML I tried the following query on Report Designer and it works:

    ... like '%' + ${MYPARAMETER} + '%' ...

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.