Hierarchy_Select: Operator equivalent to "NOT IN"

Hi @JuniSihombing 

You don’t have the (NOT) IN logic of SQL in hierarchy select.

Depends on the logic and business flow, you might want to create a custom view with your ‘IN’ logic and hierarchy select from that view.

Cheers!

@JuniSihombing 

Thanks for the info.

I could not find it documented anywhere. Do you have any document that mention this?

Cheers!

@JuniSihombing

Seems like it works for numbers only, Like task_id.

For example with task_status

<hierarchy_select result_name="result01" max_rows="1">
  <primary_table>task</primary_table>
  <attrs>
    <attr>task.task_id</attr>
  </attrs>
  <from>
    <table>task</table>
  </from>
  <where>
    <data_constraint>
      <constraint>
        <left_operand>task.task_status</left_operand>
        <operator>IN</operator>
        <right_operand>(CANCELED, OPEN)</right_operand>
      </constraint>
    </data_constraint>
  </where>
</hierarchy_select>

Gives an error

<metrix_response result_name="inProgressTasksOnRequest01">
  <result type="Exception">
    <error>
      <application_error>
        <severity>ERROR</severity>
        <message>The argument task_status does not contain a valid value.</message>
      </application_error>
    </error>
  </result>
</metrix_response>

Cheers!

<hierarchy_select result_name="result01" max_rows="1">
  <primary_table>task</primary_table>
  <attrs>
    <attr>task.task_id</attr>
  </attrs>
  <from>
    <table>task</table>
  </from>
  <where>
    <data_constraint>
      <constraint>
        <left_operand>task.task_status</left_operand>
        <operator>IN</operator>
        <right_operand>(CANCELED, OPEN)</right_operand>
      </constraint>
    </data_constraint>
  </where>
</hierarchy_select>

<metrix_response result_name="inProgressTasksOnRequest01">
  <result type="Exception">
    <error>
      <application_error>
        <severity>ERROR</severity>
        <message>The argument task_status does not contain a valid value.</message>
      </application_error>
    </error>
  </result>
</metrix_response>

@Shneor Cheshin.

for string value, the value can be put in single quote, like in SQL query.  

 <right_operand>('CANCELED', 'OPEN')</right_operand>

Cheers,

~Juni

Hi @JuniSihombing ,

Try it out and let me know if that works for you!

Regards,

Piumal