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!

Hi @Shneor Cheshin,

thank you for your feedback.  Seems “NOT IN” is not supported. 

FYI, “IN” operator can be used in hierarchy_select.

Here is example:

<hierarchy_select>
        <primary_table>task</primary_table>
        <from>
            <table>task</table>
        </from>
        <attrs>
            <attr>task.task_id</attr>
        </attrs>
        <where>
            <data_constraint>
                <constraint>
                    <left_operand>task.task_id</left_operand>
                    <operator>IN</operator>
                    <right_operand>(12080,12083,12086,47754,47755)</right_operand>
                </constraint>
            </data_constraint>
        </where>
</hierarchy_select>

Cheers,

~Juni

@JuniSihombing 

Thanks for the info.

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

Cheers!

Hi @Shneor Cheshin, 

unfortunately I don’t have documentation mentioning this.

Just doing trial and error. :D

@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!

Hello @JuniSihombing 

As per the official documentation the following operators can be used.

Here is a list of items which can be used in 'where’ constraints

And here are operators that can use for 'join’ constraints.

And here is a sample where clause with join constraints and data constraints.

Hope this helps!

Please accept the answer if it solved your query, so someone else can learn from it too!

Thanks in advance!

Regards,

/Piumal Kulasekara
 

<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

Hello @JuniSihombing 

As per the official documentation the following operators can be used.

Here is a list of items which can be used in 'where’ constraints

Operator	Description
eq	Equal to
ge	Greater than or equal to
gt	Greater than
le	Less than or equal to
li	Like
lt	Less than
ne	Not equal to
nl	Not like
not_null	Is not null
null	Is null

 

And here are operators that can use for 'join’ constraints.

Operator	Description
equi	Equi join
left_outer	Left outer join
right_outer	Right outer join

 

And here is a sample where clause with join constraints and data constraints.

 

 

Hope this helps!

 

 

Please accept the answer if it solved your query, so someone else can learn from it too!

Thanks in advance!

Regards,

/Piumal Kulasekara
 

Hi @eluxpiumal ,

thank you for your response.  

Cheers,

~Juni

Hi @JuniSihombing ,

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

Regards,

Piumal