4 Replies Latest reply: Feb 22, 2017 6:41 PM by Marco Wedel RSS

    Error in Expression ')' complex IF Statement

    Martha Parsons

      Hello,

       

      Without this IF statement it runs fine. I cannot see what I am missing.At first I had

      IF(  (cccccccccccc.last_name   <> '' and cccccccccccc.first_name + ' ' + cccccccccccc.last_name    <> 'John Smith'), (ccccccccccc.last_name + ' ' + ccccccccccc.first_name),

      IF(  (ccccccccccc.last_name     <> '' and ccccccccccc.first_name + ' ' + ccccccccccc.last_name       <> 'John Smith'), (cccccccccc.last_name + ' ' + cccccccccc.first_name),

      IF(  (cccccccccc.last_name       <> '' and cccccccccc.first_name + ' ' + cccccccccc.last_name          <> 'John Smith'), (ccccccccc.last_name + ' ' + ccccccccc.first_name),

      IF(  (ccccccccc.last_name        <> '' and ccccccccc.first_name + ' ' + ccccccccc.last_name              <> 'John Smith'), (cccccccc.last_name + ' ' + cccccccc.first_name),

      IF(  (cccccccc.last_name         <> '' and cccccccc.first_name + ' ' + cccccccc.last_name                  <> 'John Smith'), (ccccccc.last_name + ' ' + ccccccc.first_name,

      IF(  (ccccccc.last_name          <> '' and ccccccc.first_name + ' ' + ccccccc.last_name                      <> 'John Smith'), (cccccc.last_name + ' ' + cccccc.first_name),

      IF(  (cccccc.last_name           <> '' and cccccc.first_name + ' ' + cccccc.last_name                          <> 'John Smith'), (ccccc.last_name + ' ' + ccccc.first_name),

      IF(  (ccccc.last_name            <> '' and ccccc.first_name + ' ' + ccccc.last_name                              <> 'John Smith'), (cccc.last_name + ' ' + cccc.first_name),

      IF(  (cccc.last_name             <> '' and cccc.first_name + ' ' + cccc.last_name                                  <> 'John Smith'), (ccc.last_name + ' ' + ccc.first_name),

      IF(  (ccc.last_name              <> '' and ccc.first_name + ' ' + ccc.last_name                                      <> 'John Smith'), (cc.last_name + ' ' + cc.first_name),

      IF(  (cc.last_name                <> '' and cc.first_name + ' ' + cc.last_name                                         <> 'John Smith'), (cc.last_name + ' ' + cc.first_name),'UNKNOWN')))))))))))) AS [leader]

       

      And after reading some discussions, I changed it to below hoping that would fix it. Unfortunately I still get the error. This was created in Visual Studio with a CASE WHEN ELSE statement and I changed it to and IF.

       

       

      IF(  (ISNULL(cccccccccccc.last_name)  and cccccccccccc.first_name + ' ' + cccccccccccc.last_name    <> 'John Smith'), (ccccccccccc.last_name + ' ' + ccccccccccc.first_name),

      IF(  (ISNULL(ccccccccccc.last_name)   and ccccccccccc.first_name + ' ' + ccccccccccc.last_name       <> 'John Smith'), (cccccccccc.last_name + ' ' + cccccccccc.first_name),

      IF(  (ISNULL(cccccccccc.last_name)    and cccccccccc.first_name + ' ' + cccccccccc.last_name          <> 'John Smith'), (ccccccccc.last_name + ' ' + ccccccccc.first_name),

      IF(  (ISNULL(ccccccccc.last_name)      and ccccccccc.first_name + ' ' + ccccccccc.last_name              <> 'John Smith'), (cccccccc.last_name + ' ' + cccccccc.first_name),

      IF(  (ISNULL(cccccccc.last_name)       and cccccccc.first_name + ' ' + cccccccc.last_name                  <> 'John Smith'), (ccccccc.last_name + ' ' + ccccccc.first_name,

      IF(  (ISNULL(ccccccc.last_name)         and ccccccc.first_name + ' ' + ccccccc.last_name                      <> 'John Smith'), (cccccc.last_name + ' ' + cccccc.first_name),

      IF(  (ISNULL(cccccc.last_name)          and cccccc.first_name + ' ' + cccccc.last_name                          <> 'John Smith'), (ccccc.last_name + ' ' + ccccc.first_name),

      IF(  (ISNULL(ccccc.last_name)            and ccccc.first_name + ' ' + ccccc.last_name                              <> 'John Smith'), (cccc.last_name + ' ' + cccc.first_name),

      IF(  (ISNULL(cccc.last_name)              and cccc.first_name + ' ' + cccc.last_name                                  <> 'John Smith'), (ccc.last_name + ' ' + ccc.first_name),

      IF(  (ISNULL(ccc.last_name)                and ccc.first_name + ' ' + ccc.last_name                                      <> 'John Smith'), (cc.last_name + ' ' + cc.first_name),

      IF(  (ISNULL(cc.last_name)                  and cc.first_name + ' ' + cc.last_name                                         <> 'John Smith'), (cc.last_name + ' ' + cc.first_name),'UNKNOWN')))))))))))) AS [leader]

       

      Thank you,

       

      Martha Parsons

        • Re: Error in Expression ')' complex IF Statement
          Sunny Talwar

          Try replace + with &

           

          IF(  (cccccccccccc.last_name   <> '' and cccccccccccc.first_name & ' ' & cccccccccccc.last_name    <> 'John Smith'), (ccccccccccc.last_name + ' ' + ccccccccccc.first_name),

          • Re: Error in Expression ')' complex IF Statement
            Vishwarath Nagaraju

            May be try this?

             

            = IF(  Len(Trim(cccccccccccc.last_name))= 0  and cccccccccccc.first_name + ' ' + cccccccccccc.last_name    <> 'John Smith', ccccccccccc.last_name + ' ' + ccccccccccc.first_name,

            IF(  Len(Trim(ccccccccccc.last_name))= 0      and ccccccccccc.first_name + ' ' + ccccccccccc.last_name       <> 'John Smith', cccccccccc.last_name + ' ' + cccccccccc.first_name,

            IF(  Len(Trim(cccccccccc.last_name))= 0       and cccccccccc.first_name + ' ' + cccccccccc.last_name          <> 'John Smith',  ccccccccc.last_name + ' ' + ccccccccc.first_name,

            IF(  Len(Trim(ccccccccc.last_name))= 0        and ccccccccc.first_name + ' ' + ccccccccc.last_name              <> 'John Smith', cccccccc.last_name + ' ' + cccccccc.first_name,

            IF(  Len(Trim(cccccccc.last_name))= 0         and cccccccc.first_name + ' ' + cccccccc.last_name                  <> 'John Smith', ccccccc.last_name + ' ' + ccccccc.first_name

            IF(  Len(Trim(ccccccc.last_name))= 0          and ccccccc.first_name + ' ' + ccccccc.last_name                      <> 'John Smith', cccccc.last_name + ' ' + cccccc.first_name,

            IF(  Len(Trim(cccccc.last_name))= 0           and cccccc.first_name + ' ' + cccccc.last_name                          <> 'John Smith', ccccc.last_name + ' ' + ccccc.first_name,

            IF(  Len(Trim(ccccc.last_name))= 0            and ccccc.first_name + ' ' + ccccc.last_name                              <> 'John Smith', cccc.last_name + ' ' + cccc.first_name,

            IF(  Len(Trim(cccc.last_name))= 0             and cccc.first_name + ' ' + cccc.last_name                                  <> 'John Smith', ccc.last_name + ' ' + ccc.first_name,

            IF(  Len(Trim(ccc.last_name))= 0              and ccc.first_name + ' ' + ccc.last_name                                      <> 'John Smith', cc.last_name + ' ' + cc.first_name,

            IF(  Len(Trim(cc.last_name))= 0               and cc.first_name + ' ' + cc.last_name                                         <> 'John Smith', cc.last_name + ' ' + cc.first_name,'UNKNOWN'

             

             

              ))))))))))) AS [leader]

            • Re: Error in Expression ')' complex IF Statement
              Marco Wedel

              You missed one bracket in your original expression (and put it at the end instead):

               

              IF(  (cccccccccccc.last_name   <> '' and cccccccccccc.first_name + ' ' + cccccccccccc.last_name    <> 'John Smith'), (ccccccccccc.last_name + ' ' + ccccccccccc.first_name),

              IF(  (ccccccccccc.last_name     <> '' and ccccccccccc.first_name + ' ' + ccccccccccc.last_name       <> 'John Smith'), (cccccccccc.last_name + ' ' + cccccccccc.first_name),

              IF(  (cccccccccc.last_name       <> '' and cccccccccc.first_name + ' ' + cccccccccc.last_name          <> 'John Smith'), (ccccccccc.last_name + ' ' + ccccccccc.first_name),

              IF(  (ccccccccc.last_name        <> '' and ccccccccc.first_name + ' ' + ccccccccc.last_name              <> 'John Smith'), (cccccccc.last_name + ' ' + cccccccc.first_name),

              IF(  (cccccccc.last_name         <> '' and cccccccc.first_name + ' ' + cccccccc.last_name                  <> 'John Smith'), (ccccccc.last_name + ' ' + ccccccc.first_name),

              IF(  (ccccccc.last_name          <> '' and ccccccc.first_name + ' ' + ccccccc.last_name                      <> 'John Smith'), (cccccc.last_name + ' ' + cccccc.first_name),

              IF(  (cccccc.last_name           <> '' and cccccc.first_name + ' ' + cccccc.last_name                          <> 'John Smith'), (ccccc.last_name + ' ' + ccccc.first_name),

              IF(  (ccccc.last_name            <> '' and ccccc.first_name + ' ' + ccccc.last_name                              <> 'John Smith'), (cccc.last_name + ' ' + cccc.first_name),

              IF(  (cccc.last_name             <> '' and cccc.first_name + ' ' + cccc.last_name                                  <> 'John Smith'), (ccc.last_name + ' ' + ccc.first_name),

              IF(  (ccc.last_name              <> '' and ccc.first_name + ' ' + ccc.last_name                                      <> 'John Smith'), (cc.last_name + ' ' + cc.first_name),

              IF(  (cc.last_name                <> '' and cc.first_name + ' ' + cc.last_name                                         <> 'John Smith'), (cc.last_name + ' ' + cc.first_name),'UNKNOWN')))))))))))) AS [leader]

               

               

              hope this helps

               

              regards

               

              Marco