CODEWITHSUNDEEP
informaticaiif-function
Frederica
Frederica

Reputation: 35

Can you assist with wrong informatica results?

Hello I am new to informatica, and was trying to write a decode or if, where only one value goes into the output port... a value that is not equal is going into the output port... i iam positive the value is not equal... so my decode doesn't work and me trying to do a nested if doesn't work... the decode is as follows... decode (true testopen = testdoor1, 'open', 'close', testopen = testdoor2, 'open', 'close', testopen = testdoor3, 'open', 'close')

Although i know testdoor3 is the only one that equals testopen.. testdoor3 is populated.. i tested it with one test on testdoor2 ( only one expression in the decode and it still populates testopen with testopen2) the testdoor2 and testdoor1 have nulls

i then tried with an iif but got stuck afteer the first

 iif (testopen = testdoor1, 'equal','noequal')

but i couldn't figure out how to do it with 3 iif statements.. your help is appreciated.

decode (true
        testopen = testdoor1, 'open', 'close',
        testopen = testdoor2, 'open', 'close',
        testopen  = testdoor3, 'open', 'close')

or iif (testopen = testdoor1, 'equal','noequal') your assistance is appreciated!

Upvotes: 0

Views: 82

Answers (2)

Dylan Naorem
Dylan Naorem

Reputation: 11

Here is the syntax of DECODE from Informatica documentation

DECODE( value, first_search, first_result [, second_search, second_result]...[,default] )

I would suggest you to add what your input values are and what the expected output is.

By looking at your code, it seems you are not providing correct input to the intended positions/parameters. Let's say the ports have below values

  1. testopen='Yes'

  2. testdoor1='Yes'

  3. testdoor2='No'

  4. testdoor3='Yes'

Let's see after replacing your decode with the actual value from the port and compare it with the syntax. Notice the (second_search) where you are not comparing the ports but just the value 'close'. This is why your code is behaving weirdly.

Note: NOTICE the missing comma after 'true' in your original post

decode(true,
            'Yes' = 'No' (first_search), 'open' (first_result), 'close' (second_search),
            'Yes' = 'Yes' (second_result), 'open' (third_search), 'close' (third_result),
            'Yes' = 'No' (forth_search), 'open' (forth_result), 'close'(default))

Try using similar to below code. This is again from Informatica documentation.

DECODE( TRUE,
        Var1 = 22, 'Variable 1 was 22!',
        Var2 = 49, 'Variable 2 was 49!',
        Var1 < 23, 'Variable 1 was less than 23.',
        Var2 > 30, 'Variable 2 was more than 30.',
        'Variables were out of desired ranges.')

Upvotes: 1

NickW
NickW

Reputation: 9788

DECODE takes a value, pairs of search and result values and, optionally,a default.

You seem to be using triplets instead of pairs of parameters. What you have actually written is:

decode (true
        testopen = testdoor1, 'open', 
        'close',  testopen = testdoor2, 
        'open', 'close',
        testopen  = testdoor3,
        'open', 'close')

Upvotes: 0

Related Questions