The Decode function expects "pairs" of condition and return value; using True as the first condition does not make any sense.
Replace the double quotes with single quotes.
You might also need to convert the Age port (which seems to be string) into an integer using the TO_INTEGER function when checking it's value.
Of course you are right regarding the single quotes instead of the double quotes, but your statement about the DECODE( TRUE...) might need some clarification from my side.
Using DECODE( TRUE, ...) is a very common technique in PowerCenter to implement a CASE WHEN statement. In general, DECODE( TRUE, ...) allows you to use distinct conditions (which need not have anything in common) and check them one after the other.
For example, let's suppose you have check whether a person may buy certain items in a grocery store. First you have to check whether this person is of age 21 or older; if that's not the case, you have to distinguish between being at least 18 years of age and being younger than 18 but having a written permission from the parents (in this example it's an additional input port named PARENTAL_PERM of type String). Then the DECODE chain might look like this:
age >= 21, 'FULL',
age >= 18, 'ONLY LIGHT ALCOHOL',
PARENTAL_PERM = 'YES', 'ANYTHING BUT ALCOHOL',
That's far easier to read and to maintain (e.g. when extending the list of conditions or changing single conditions) than nested IIF() invocations.
Also - believe it or not - one former colleague of mine from Informatica Professional Services has tested the performance thoroughly and has found out that a DECODE( TRUE, ...) always works faster (even with only one condition) than an equivalent IIF() invocation. That's the reason why I (almost) always use DECODE() instead of IIF() . And why I keep on explaining to everybody why I'm doing so.
thanks for the clarification
My apologies for always playing the know-it-all
I tried the below as per your suggestion:
(ISNULL(First_Name) OR First_Name='') OR (ISNULL(Last_Name) OR Last_Name='') OR
(ISNULL(TO_INTEGER(Age)) or TO_INTEGER(Age)=''),'35',
I am still getting the below error:
ERROR: "<<PM Parse Error>> [=]: function cannot resolve operands of ambiguously mismatching types"
The port was of type integer initially but i tried the same by making it as String.
What data type is the Age port? String or Integer?
Checking for an empty string on an integer field doesn't make sense to me, if there is no value it will be NULL.
Thanks Andre. Got you.
It is Integer.
Let me modify that and check.