Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: MQL related question - what should be the syntax

  1. #1

    Default MQL related question - what should be the syntax

    Hi,
    I am trying to add a condition where some fields are ANDed and then there is one condition which should be ORed.

    In plain sql it would be like
    WHERE
    TABLEA.COLA = 23
    AND TABLEA.COLB = 2012
    AND (TABLEB.COLA = 20932598 OR TABLEA.COLC = 20932598)

    In MQL I added it like:

    AND ( [TABLEA.COLA] = 23;
    [TABLEA.COLB] = 2012;
    ( OR ( [TABLEB.COLA] = 20932598 ;
    [TABLEA.COLC] = 20932598 )
    )
    )



    Caused by: org.pentaho.pms.core.exception.PentahoMetadataException: SqlOpenFormula.ERROR_0007 - An unknown error occurred with the formula
    AND ( [TABLEA.COLA] = 23; [TABLEA.COLB] = 2012; ( OR ( [TABLEB.COLA] = 20932598; [TABLEA.COLC] = 20932598 ) ) ). See the log for details.
    at org.pentaho.metadata.query.impl.sql.SqlOpenFormula.parseAndValidate(SqlOpenFormula.java:240)
    at org.pentaho.metadata.query.impl.sql.SqlGenerator.getSQL(SqlGenerator.java:414)
    at org.pentaho.metadata.query.impl.sql.SqlGenerator.generateSql(SqlGenerator.java:362)
    at org.pentaho.platform.plugin.services.connections.metadata.sql.SqlMetadataQueryExec.executeQuery(SqlMetadataQueryExec.java:80)


    Please suggest how this can be implemented.

    Thanks,
    Nick

  2. #2

    Default

    I think i found the solution.

    The issue was that extra pair of brackets around OR condition. So the correct format would be:

    AND ( [TABLEA.COLA] = 23; [TABLEA.COLB] = 2012;
    OR ( [TABLEB.COLA] = 20932598 ; [TABLEA.COLC] = 20932598 )
    )


    Thank You!

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.