6 Replies Latest reply on Oct 27, 2019 11:31 PM by Nico Heinze

    Expression will not work

    theladyd Active Member

      Why would this expression work in one mapping, but not another? I've created a column to list the event_dt if the the event_cd is not blank or null, but the expression seem to be ignored altogether.  However, the same expression is working in another mapping in the same folder.

      IIF(fieldname  != ' ', fieldname1)

       

      Source and Target database is Oracle.

       

      PowerCenter 10.1

        • 1. Re: Expression will not work
          Nico Heinze Guru

          No offense intended, but this is a perfect example how NOT to build an expression term for various reasons.

           

          First the IIF() function doesn't have the "else" case, meaning its return value is pretty much undetermined (i.e. random) if the condition doesn't apply.

           

          Second comparing a field with a single blank may or may not work, depending on the source data and the exact data type. For example, if the source attribute is a CHAR attribute with at least two characters of length, then this condition will NEVER apply: a CHAR attribute is always read in its entire defined length, which - in my example - would mean that it will always be read from the source DB with a length of two characters. But the condition only checks for one blank, and one blank can never equal two blanks.

          Why does this happen? That's one of the big differences between VARCHAR (or VARCHAR2 in Oracle) and CHAR attributes: CHAR attributes are always stored in their defined length, and if a value to be stored is shorter than this defined length, it will be padded with blank spaces to the defined length. For VARCHAR attributes this doesn't apply.

           

          In short: the fact that this "same" expression seems to work differently in different mappings doesn't tell anything.

           

          Regards,

          Nico

          • 2. Re: Expression will not work
            theladyd Active Member

            No offense taken, Nico.  I have considered and tested spaces, length, etc.  It is Varchar2 for both source and target.

            The mapping that works is from the same source.  Thank you, for your reply.

            • 3. Re: Expression will not work
              pgeorge1 Guru

              Please elaborate on what exactly is meant by "ignored altogether".

              Are you saying it always returns NULL?

              • 4. Re: Expression will not work
                Nico Heinze Guru

                To add to Paul's question, what is the expected output?

                The Transformation Language guide clearly states that IIF - if the condition is not met and there is no "else" value given - will return a 0, an empty string, or a NULL (depending on the data type of the port).

                 

                Regards,

                Nico

                • 5. Re: Expression will not work
                  theladyd Active Member

                  Yes, it always return null

                  • 6. Re: Expression will not work
                    Nico Heinze Guru

                    Could you please check (e.g. using some "dummy" mapping) which distinct values are contained in column fieldname? For examply, by performing a Distinct sort (Distinct option of the Sorter transformation) and writing these values enclosed in some delimiters to a flat file?

                     

                    Regards,

                    Nico