CASE WHEN syntax error

Ask the Community
1

I am trying to create a new dimension field into an existing model. I have followed indications in these two pages:

That if i have understood correctly are for sql and aql respectively.
Both are giving me errors:

Model my_model {
 ...
  dimension mode_simplistic {
    label: 'Mode simplistic'
    type: 'text'
    hidden: false
    definition: @sql {{ 
      CASE
        WHEN {{ #SOURCE.imaging_mode }} = 'spot' then 'spot'
        WHEN {{ #SOURCE.imaging_mode }} = 'strip' then 'strip'
        WHEN {{ #SOURCE.imaging_mode }} = 'topsar' then 'topsar'
      END
    }};; 
  }
}

Gives me the error:

S1000 (80) [Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: org.apache.hive.service.cli.HiveSQLException: Error running query: [PARSE_SYNTAX_ERROR] org.apache.spark.sql.catalyst.parser.ParseException: [PARSE_SYNTAX_ERROR] Syntax error at or near '{'. SQLSTATE: 42601 (line 5, pos 4) == SQL == /* - Job ID: 2199072

And for aql:

  dimension my_model {
    label: 'Mode simplistic'
    type: 'text'
    hidden: false
    definition: @aql case(when: {{ my_model.imaging_mode }} == 'spot', then: 'spot') ;; 
  }

Syntax error. 
> 1 | case(when: {{ prod_reporting_dim_tasks.imaging_mode }} == 'spot', then: 'spot') 
    |             ^

Expected one of the following: 

'//', /\s/, an identifier
(ERR-1)

I would appreciate help on this

2

It worked like this:

  dimension mode_simplistic {
    label: 'Mode simplistic'
    type: 'text'
    hidden: false
    definition: @sql 
      CASE
        WHEN {{ #SOURCE.imaging_mode }} ilike '%spot%' then 'spot'
        WHEN {{ #SOURCE.imaging_mode }} ilike '%strip%' then 'strip'
        WHEN {{ #SOURCE.imaging_mode }} ilike '%topsar%' then 'topsar'
      END
    ;; 
  }
3

For AQL, you don’t need the curly brackets to refer to a field, you can just do this:

@aql case(when: my_model.imaging_mode == 'spot', then: 'spot') ;;