1. Using Incentive Compensation
  2. Logical Operations Best Practices and Quick Reference

Logical Operations Best Practices and Quick Reference

You can include logical operations when you build expressions. Here are best practices and a quick reference table of available logical operations.

Best Practices

Follow these best practices when you use logical operations in expressions.

  • Calculated dates may include the entire time stamp and calculated numbers may return a number with decimals. For a calculated number, use the ROUND function, and for calculated dates use TRUNC when appropriate.

  • While comparing strings, ensure their case matches to prevent incorrect responses. Use the UPPER or LOWER function as needed.

  • To avoid possible null values use the NVL function.

  • Usage of all Logical operations (which only have a Boolean output) is restricted to within the input condition of the CHOICE statement. This includes IS_GREATER, IS_LESS, IS_EQUAL, IS_NULL, NOT, IS_IN, IS_BETWEEN, AND, OR, and IS_LIKE.

  • Strings are compared in lexical orders when used in comparison functions. Use them appropriately.

  • While comparing multiple inputs, match the types. Use TO_NUMBER or TO_CHAR or TO_DATE functions as needed.

Quick Reference

This table lists the syntax, inputs, return data type, and examples for logical operations.

Syntax

Inputs

Return Data Type

Example

CHOICE (Condition 1*, Value 1*, Condition 2, Value 2, ...Condition n, Value n, Value Z*)

  1. Condition 1*: Boolean

  2. Value 1*: String or date or number

  3. Condition 2 ...n: Boolean

  4. Values 2 ...n: String or date or number

  5. Value Z*: String or date or number

String, Number or Date (Same as Value)

  1. CHOICE (IS_GREATER (Credit.Credit Amount, Measure.Target), Credit.Credit Amount, Measure.Target)

  2. CHOICE (IS_GREATER (Credit.Credit Amount, 1000), 1, IS_EQUAL (Credit.Credit Amount, TO_NUMBER(Measure result.All Product Sales.ITD Output Achieved)), 2, 3)

IS_EQUAL (Value 1*, Value 2*)

  1. Value 1*: String or date or number

  2. Value 2*: String or date or number

Boolean

  1. IS_EQUAL (Credit Category Name (12313331333), 'Servers')2. IS_EQUAL (Credit.Credit Amount, Measure.Target)

  2. IS_EQUAL (TO_DATE('1-Dec-2017'), TO_DATE('2-Dec-2017'))

IS_NULL (Value*)

Value*: String or date or number or Boolean

Boolean

IS_NULL (Credit Category Name (12313331333)

IS_GREATER (Value 1*, Value 2*)

  1. Value 1*: Date or number

  2. Value 2*: Date or number

Boolean

  1. IS_GREATER (Credit.Credit Amount, Measure.Target)

  2. IS_GREATER (TO_DATE('11-Dec-2017'), TO_DATE('2-Dec-2017'))

IS_LESS (Value 1*, Value 2*)

  1. Value 1*: Date or number

  2. Value 2*: Date or number

Boolean

  1. IS_LESS (Credit.Credit Amount, Measure.Target)

  2. IS_LESS (TO_DATE('11-Dec-2017'), TO_DATE('2-Dec-2017'))

IS_BETWEEN (Value to evaluate*, Minimum Value*, Maximum Value*)

  1. Value to evaluate*: Date or number

  2. Minimum Value*: Date or number

  3. Maximum Value*: Date or number

Boolean

  1. IS_BETWEEN (Credit.Credit Amount, TO_NUMBER(Plan component result.Management Bonus.PTD Output Achieved), Measure.Target)

  2. IS_BETWEEN (TO_DATE('11-Dec-2017'), TO_DATE('2-Dec-2017'), TO_DATE('22-Dec-2017'))

AND (Condition 1*, Condition 2, ...Condition n)

  1. Condition 1*: Boolean

  2. Condition 2*: Boolean

  3. Condition 3 to n: Boolean

Boolean

AND (IS_GREATER (Credit.Credit Amount, Measure.Target), IS_EQUAL (Credit Category Name (12313331333), 'Servers'))

OR (Condition 1*, Condition 2, ...Condition n)

  1. Condition 1*: Boolean

  2. Condition 2*: Boolean

  3. Condition 3 to n: Boolean

Boolean

OR (IS_GREATER (Credit.Credit Amount, Measure.Target), IS_EQUAL (Credit Category Name (12313331333), 'Servers'))

NOT (Condition*)

Condition*: Boolean

Boolean

  1. NOT (IS_LESS (2000, TO_NUMBER (Measure result.All Product Sales.ITD Output Achieved))))

  2. NOT (IS_EQUAL (Credit Category Name (12313331333), 'Servers'))

IS_LIKE (Value 1*, Value 2*)

  1. Value 1*: String or number

  2. Value 2*: String or number

Boolean

  1. IS_LIKE (Credit Category Name (12313331333), 'Servers')

  2. IS_LIKE (120, '12%')

  3. IS_LIKE (UPPER(Participant.Currency Code), 'U%')

IS_IN (Value*, In Value 1*, ...In Value n)

  1. Value*: String or date or number

  2. In Value 1*: String or date or number

  3. In Value 3 to n: String or date or number

Boolean

  1. IS_IN (Credit Category Name (12313331333), 'Servers', 'Laptops', 'Desktops')

  2. IS_IN (Credit.Credit Amount, TO_NUMBER(Measure result.All Product Sales.ITD Output Achieved), Measure.Target, Measure.ITD Target)

Related Topics