TRANSLATE function

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • kovariadam@gmail.com
    #1

    TRANSLATE function

    Hi,
    Does anybody know why i get this error:
    SQL0176N The second, third or fourth argument of the TRANSLATE scalar
    function is incorrect. SQLSTATE=42815

    with this query:
    SELECT TRANSLATE(p.men o,
    'aaaaccdeeeiill nnoooorrsstuuuu yzzAAAACCDEEEII LLNNOOOORRSSTUU UUYZZ',
    'áâãäæèïéìëíîåµ ñòóôöõàø¶¹»úüûù ý¼¾ÁÂÃÄÆÈÏÉÌËÍΠťÑÒÓÔÖÕÀئ©«ÚÜ ÛÙݬ®')
    FROM oa.pracovnik p;

    ---
    CREATE TABLE oa.pracovnik (
    ...
    meno CHARACTER(20),
    ...
    );

    Thanks in advance.

    Adam Kovari
    Tags: translate:p
    • Tonkuma
      #2
      Re: TRANSLATE function

      What platform and DB2 Version/Release are you using?

      Here is a result on DB2 for Windows V9.1.
      connect to SAMPLE user db2admin using ********;
      ----------------------------------------------------------
      Database Connection Information

      Database server = DB2/NT 9.1.2
      SQL authorization ID = DB2ADMIN
      Local database alias = SAMPLE

      A JDBC connection to the target has succeeded.
      -------------------- Commands Entered --------------------
      CREATE TABLE oa.pracovnik (
      meno CHARACTER(20)
      );
      ----------------------------------------------------------
      DB20000I The SQL command completed successfully.

      -------------------- Commands Entered --------------------
      SELECT TRANSLATE(p.men o,
      'aaaaccdeeeiill nnoooorrsstuuuu yzzAAAACCDEEEII LLNNOOOORRSSTUU UUYZZ',
      'áâãäæèïéìëíîåµ ñòóôöõàø¶¹»úüûù ý¼¾ÁÂÃÄÆÈÏÉÌËÍΠťÑÒÓÔÖÕÀئ©«ÚÜ ÛÙݬ®')
      FROM oa.pracovnik p;
      ----------------------------------------------------------

      1
      --------------------

      0 record(s) selected.

        Comment

        • Lennart
          #3
          Re: TRANSLATE function

          On Jul 1, 9:42 pm, "kovaria...@gma il.com" <kovaria...@gma il.com>
          wrote:
          Hi,
          Does anybody know why i get this error:
          SQL0176N The second, third or fourth argument of the TRANSLATE scalar
          function is incorrect. SQLSTATE=42815
          >
          with this query:
          SELECT TRANSLATE(p.men o,
          'aaaaccdeeeiill nnoooorrsstuuuu yzzAAAACCDEEEII LLNNOOOORRSSTUU UUYZZ',
          'áâãäæèïéìëíîåµ ñòóôöõàø¶¹»úüûù ý¼¾ÁÂÃÄÆÈÏÉÌËÍΠťÑÒÓÔÖÕÀئ©«ÚÜ ÛÙݬ®')
          FROM oa.pracovnik p;
          >
          ---
          CREATE TABLE oa.pracovnik (
          ...
          meno CHARACTER(20),
          ...
          );
          >
          I don't know the answer and I don't have the time to figure it out
          right now, but you can use db2 to get more info about the error:

          [lelle@53dbd181 ~]$ db2 "? SQL0176N"

          SQL0176N The second, third or fourth argument of the TRANSLATE scalar
          function is incorrect.

          Explanation:

          The statement is not correct for one or more of the following
          reasons:
          * The translate scalar function does not allow replacement of a
          character by another character which is encoded using a different
          number of bytes. For example, a single-byte character cannot be
          replaced with a double-byte character nor can a double-byte
          character
          be replaced with a single-byte character.
          * The second and third arguments of the translate scalar function
          must
          end with correctly formed characters.
          * The fourth argument of the translate scalar function must be a
          correctly formed single-byte character if the first argument is
          CHAR
          or VARCHAR.
          * The fourth argument of the translate scalar function must be a
          correctly formed double-byte character if the first argument is
          GRAPHIC or VARGRAPHIC.

          The statement cannot be processed.

          User response:

          Ensure that the second, third, and fourth arguments of the translate
          scalar function have correct values.



          /Lennart

            Comment

            • kovariadam@gmail.com
              #4
              Re: TRANSLATE function

              Thank you for your effort.
              I'm using DB2 Express-C 9.5 on Windows XP 32.
              I' ve found, just a minut ago, that it works on databases with
              codepage 1250, but not with utf-8, therefore i have converted my
              database.

              Thank you very much!

              On 1. Júl, 22:13 h., Tonkuma <tonk...@fiberb it.netwrote:
              What platform and DB2 Version/Release are you using?
              >
              Here is a result on DB2 for Windows V9.1.
              connect to SAMPLE user db2admin using ********;
              ----------------------------------------------------------
                 Database Connection Information
              >
               Database server        = DB2/NT 9.1.2
               SQL authorization ID   = DB2ADMIN
               Local database alias   = SAMPLE
              >
              A JDBC connection to the target has succeeded.
              -------------------- Commands Entered --------------------
              CREATE TABLE oa.pracovnik (
                      meno            CHARACTER(20)
              );
              ----------------------------------------------------------
              DB20000I  The SQL command completed successfully.
              >
              -------------------- Commands Entered --------------------
              SELECT TRANSLATE(p.men o,
              'aaaaccdeeeiill nnoooorrsstuuuu yzzAAAACCDEEEII LLNNOOOORRSSTUU UUYZZ',
              'áâãäæèïéìëíîåµ ñòóôöõàø¶¹»úüûù ý¼¾ÁÂÃÄÆÈÏÉÌËÍΠťÑÒÓÔÖÕÀئ©«ÚÜ ÛÙݬ®')
              FROM oa.pracovnik p;
              ----------------------------------------------------------
              >
              1
              --------------------
              >
                0 record(s) selected.

                Comment

                • kovariadam@gmail.com
                  #5
                  Re: TRANSLATE function

                  Thanks, already solved.

                  On 1. Júl, 22:20 h., Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
                  On Jul 1, 9:42 pm, "kovaria...@gma il.com" <kovaria...@gma il.com>
                  wrote:
                  >
                  >
                  >
                  >
                  >
                  Hi,
                  Does anybody know why i get this error:
                  SQL0176N  The second, third or fourth argument of the TRANSLATE scalar
                  function is incorrect.  SQLSTATE=42815
                  >
                  with this query:
                  SELECT TRANSLATE(p.men o,
                  'aaaaccdeeeiill nnoooorrsstuuuu yzzAAAACCDEEEII LLNNOOOORRSSTUU UUYZZ',
                  'áâãäæèïéìëíîåµ ñòóôöõàø¶¹»úüûù ý¼¾ÁÂÃÄÆÈÏÉÌËÍΠťÑÒÓÔÖÕÀئ©«ÚÜ ÛÙݬ®')
                  FROM oa.pracovnik p;
                  >
                  ---
                  CREATE TABLE oa.pracovnik (
                     ...
                          meno            CHARACTER(20),
                     ...
                  );
                  >
                  I don't know the answer and I don't have the time to figure it out
                  right now, but you can use db2 to get more info about the error:
                  >
                  [lelle@53dbd181 ~]$ db2 "? SQL0176N"
                  >
                  SQL0176N  The second, third or fourth argument of the TRANSLATE scalar
                        function is incorrect.
                  >
                  Explanation:
                  >
                  The statement is not correct for one or more of the following
                  reasons:
                  *  The translate scalar function does not allow replacement of a
                     character by another character which is encoded using a different
                     number of bytes. For example, a single-byte character cannot be
                     replaced with a double-byte character nor can a double-byte
                  character
                     be replaced with a single-byte character.
                  *  The second and third arguments of the translate scalar function
                  must
                     end with correctly formed characters.
                  *  The fourth argument of the translate scalar function must be a
                     correctly formed single-byte character if the first argument is
                  CHAR
                     or VARCHAR.
                  *  The fourth argument of the translate scalar function must be a
                     correctly formed double-byte character if the first argument is
                     GRAPHIC or VARGRAPHIC.
                  >
                  The statement cannot be processed.
                  >
                  User response:
                  >
                  Ensure that the second, third, and fourth arguments of the translate
                  scalar function have correct values.
                  >
                  /Lennart

                    Comment

                    Previous template Next
                    Working...