Search code examples
informaticainformatica-cloud

oracle equivalent 'IN' function in informatica

I can't believe it's so hard to find an example in informatica documentation.

I would like to check if a string column that matches any of the following strings: 'A', 'B', 'C'

In Oracle, it would be as simple as:

where column in ('A', 'B', 'C')

I have the following in the expression component

iif(column in ('1','2','3'), 'apple', iif(column in ('4','5','6','7','8'), 'orange', 'Unknown'))

and I got syntax error

Solution

  • Informatica has in() clause. Here is the syntax in (data_col, 'val1','val2', [case_flag]).
    case_flag - This can be 0 for case insensitive comparison, and non zero/default int is case sensitive. This returns 0 for false and 1 for True.
    You can change your code like -

    iif(in (column,'1','2','3')=1, 'apple', 
   iif(in (column , '4','5','6','7','8')=1, 'orange', 'Unknown')
)

    This search is case sensitive and does an exact match.