Decode Function in saved search

We can use decode function for mutilple conditions check like nested if condition.

DECODE(   (CASE     WHEN {item.type} = ‘Inventory Item’ AND {quantity} = {quantityshiprecv} THEN 1     ELSE       (CASE         WHEN {item.type} = ‘Inventory Item’ AND {quantity} != {quantityshiprecv} AND {closed} = ‘T’ THEN 1         ELSE 0       END) END  ),   1, 1, 0 )    

Using a combination of a CASE statement and the DECODE function to determine a result based on certain conditions. Let’s break it down step by step:

1. The innermost CASE statement:
   • This part checks two conditions:
     • If the item type is ‘Inventory Item’ AND
     • If the quantity is equal to the quantityshiprecv.
2. If both conditions in the inner CASE statement are true, it returns 1. This 1 is used in further calculations.
3. If the conditions in the inner CASE statement are not met, it proceeds to the ELSE part of the inner CASE statement.
4. The nested CASE statement:
   • This part checks three conditions:
     • If the item type is ‘Inventory Item’ AND
     • If the quantity is not equal to the quantityshiprecv AND
     • If the closed field is equal to ‘T’.
5. If all these conditions in the nested CASE statement are true, it also returns 1. This 1 is used in further calculations.
6. If none of the conditions in the nested CASE statement are met, it returns 0.
7. The result of the entire CASE statement:
   • The outer CASE statement now evaluates the result of the innermost CASE statement and the nested CASE statement.
   • If either of them returns 1, it assigns a value of 1. Otherwise, it assigns a value of 0.
8. Finally, the DECODE function is used to check the result of the CASE statement:
   • If the result of the CASE statement is 1, it returns 1.
   • If the result of the CASE statement is 0, it returns 0.

In simpler terms, this code is evaluating conditions related to item types, quantities, and the ‘closed’ field, and it returns 1 if specific conditions are met, or 0 if they are not. The DECODE function is essentially used to convert the result into a 1 or 0 value.