7 Replies Latest reply on Mar 26, 2020 3:43 AM by Amit Bhartiya

    Decode issue

    Amit Bhartiya Seasoned Veteran

      Hi All,


      I am trying to write a decode statement but it's failing with invalid token.
      Below is the statement:

      Decode(True,

      (ISNULL(First_Name) OR First_Name="") OR (ISNULL(Last_Name) OR Last_Name="") OR

      (ISNULL(Age) or Age=""),35,

      Age < 30, 60,95)

       

      I am trying to pass a score based on the above conditions.
      What is wrong with the statement?

        • 1. Re: Decode issue
          Andre Kirsten Guru

          Hi Amit,

           

          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.

          Kind regards,

          Andre.

          • 2. Re: Decode issue
            Nico Heinze Guru

            Hi Andre,

             

            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:

            Decode( TRUE,

              age >= 21, 'FULL',

              age >= 18, 'ONLY LIGHT ALCOHOL',

              PARENTAL_PERM = 'YES', 'ANYTHING BUT ALCOHOL',

              'NOTHING')

             

            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.

             

            Regards,

            Nico

            • 3. Re: Decode issue
              Andre Kirsten Guru

              Hi Nico,

               

              thanks for the clarification

               

              Kind regards,

               

              Andre.

              • 4. Re: Decode issue
                Nico Heinze Guru

                My apologies for always playing the know-it-all

                • 5. Re: Decode issue
                  Amit Bhartiya Seasoned Veteran

                  Hi Nico/Andre,
                  I tried the below as per your suggestion:

                  Decode(True,

                   

                   

                  (ISNULL(First_Name) OR First_Name='') OR (ISNULL(Last_Name) OR Last_Name='') OR

                   

                   

                  (ISNULL(TO_INTEGER(Age)) or TO_INTEGER(Age)=''),'35',

                   

                   

                  TO_INTEGER(Age)<=30,'60','95')

                   

                  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.




                  • 6. Re: Decode issue
                    Andre Kirsten Guru

                    Hi Amit,

                     

                    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.

                     

                    Kind regards,
                    Andre.

                    • 7. Re: Decode issue
                      Amit Bhartiya Seasoned Veteran

                      Thanks Andre. Got you.

                      It is Integer.
                      Let me modify that and check.