Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: How to use "case when" in formula field

  1. #1

    Default How to use "case when" in formula field

    Hi,

    I have trouble using the formula field in Pentaho Metadata Editor. I want to make a "case when". At the moment, it looks like this :
    Code:
    =CASE([BT_ORDER_ORDER.BC_ORDER_TYPE]="0"; 
    "Dep";
    [BT_ORDER_ORDER.BC_ORDER_TYPE]="1"; 
    "Arr";
    )
    The result is :
    Code:
    An error occurred executing SQL: SELECT DISTINCT 
              =CASE(
    [BT_ORDER_ORDER.BC_ORDER_TYPE]="0"; 
    "Dep";
    [BT_ORDER_ORDER.BC_ORDER_TYPE]="1"; 
    "Arr";
    ) AS "COL0"
    FROM 
              "myDB"."Order" "BT_ORDER_ORDER"
    
    
    ERROR: syntax error at or near "="
      Position : 28
    I tried without the "=" but it doesn't work. The option "is the Formula Exact?" is checked.
    If one of you has already used the "case" formula, could you help me please?

    Thanks.

  2. #2
    Join Date
    Jun 2013
    Posts
    44

    Default

    I'm not getting it right so unable to help but for sure will work on it to get its solution along with also looking keenly over the forum if something apt come to your way ..

  3. #3
    Join Date
    Nov 2010
    Posts
    7

    Default

    try removing that last ';' inside the case
    Last edited by tgf; 07-03-2013 at 09:53 AM.

  4. #4

    Default

    Thank you for your answers.

    I removed the ";" but it still doesn't work. It displays (same error without the ";" actually) :

    Code:
    An error occurred executing SQL: SELECT DISTINCT 
              =CASE(
    [BT_ORDER_ORDER.BC_ORDER_TYPE]="0"; 
    "Dep";
    [BT_ORDER_ORDER.BC_ORDER_TYPE]="1"; 
    "Arr"
    ) AS "COL0"
    FROM 
              "myDB"."Order" "BT_ORDER_ORDER"
    
    
    ERROR: syntax error at or near "="
      Position : 28

  5. #5
    Join Date
    Feb 2013
    Posts
    1

    Default

    I'm experiencing this same issue. I've tried using T-SQL syntax (Case when) and that works but it kills the performance of the report once its opened in the User Console and causes a ton of warnings in the Pentaho.log file. Can anyone offer any suggestions? Here's my SQL:

    An error occurred executing SQL:
    SELECT DISTINCT
    BT_FACT_REQUEST_ACTION_FACT_REQUEST_ACTION.REQUEST_ID AS COL0
    ,CASE ([BT_FACT_REQUEST_ACTION_FACT_REQUEST_ACTION.ACTIVITY_TYPE_KEY]="5";[BT_FACT_REQUEST_ACTION_FACT_REQUEST_ACTION.ACTIVITY_DATE];"NULL") AS COL1
    FROM
    FACT_REQUEST_ACTION BT_FACT_REQUEST_ACTION_FACT_REQUEST_ACTION

    Incorrect syntax near '='.

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.