TRANSLATE function
in [DB2]
|
Prev: Reorgchk, Reorg, Runstats not having any effect after completion. Why is this?
Next: Problem While loading from Client Database
From: kovariadam on 1 Jul 2008 15:42 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.meno, 'aaaaccdeeeiillnnoooorrsstuuuuyzzAAAACCDEEEIILLNNOOOORRSSTUUUUYZZ', 'áâãäæèïéìëíîåµñòóôöõàø¶¹»úüûùý¼¾ÁÂÃÄÆÈÏÉÌËÍÎÅ¥ÑÒÓÔÖÕÀئ©«ÚÜÛÙݬ®') FROM oa.pracovnik p; --- CREATE TABLE oa.pracovnik ( ... meno CHARACTER(20), ... ); Thanks in advance. Adam Kovari
From: Tonkuma on 1 Jul 2008 16:13 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.meno, 'aaaaccdeeeiillnnoooorrsstuuuuyzzAAAACCDEEEIILLNNOOOORRSSTUUUUYZZ', 'áâãäæèïéìëíîåµñòóôöõàø¶¹»úüûùý¼¾ÁÂÃÄÆÈÏÉÌËÍÎÅ¥ÑÒÓÔÖÕÀئ©«ÚÜÛÙݬ®') FROM oa.pracovnik p; ---------------------------------------------------------- 1 -------------------- 0 record(s) selected.
From: Lennart on 1 Jul 2008 16:20 On Jul 1, 9:42 pm, "kovaria...(a)gmail.com" <kovaria...(a)gmail.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.meno, > 'aaaaccdeeeiillnnoooorrsstuuuuyzzAAAACCDEEEIILLNNOOOORRSSTUUUUYZZ', > 'áâãäæèïéìëíîåµñòóôöõàø¶¹»úüûùý¼¾ÁÂÃÄÆÈÏÉÌËÍÎÅ¥ÑÒÓÔÖÕÀئ©«ÚÜÛÙݬ®') > 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(a)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
From: kovariadam on 1 Jul 2008 16:51 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...(a)fiberbit.net> wrote: > 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.meno, > 'aaaaccdeeeiillnnoooorrsstuuuuyzzAAAACCDEEEIILLNNOOOORRSSTUUUUYZZ', > 'áâãäæèïéìëíîåµñòóôöõàø¶¹»úüûùý¼¾ÁÂÃÄÆÈÏÉÌËÍÎÅ¥ÑÒÓÔÖÕÀئ©«ÚÜÛÙݬ®') > FROM oa.pracovnik p; > ---------------------------------------------------------- > > 1 > -------------------- > > 0 record(s) selected.
From: kovariadam on 1 Jul 2008 16:52
Thanks, already solved. On 1. Júl, 22:20 h., Lennart <Erik.Lennart.Jons...(a)gmail.com> wrote: > On Jul 1, 9:42 pm, "kovaria...(a)gmail.com" <kovaria...(a)gmail.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.meno, > > 'aaaaccdeeeiillnnoooorrsstuuuuyzzAAAACCDEEEIILLNNOOOORRSSTUUUUYZZ', > > 'áâãäæèïéìëíîåµñòóôöõàø¶¹»úüûùý¼¾ÁÂÃÄÆÈÏÉÌËÍÎÅ¥ÑÒÓÔÖÕÀئ©«ÚÜÛÙݬ®') > > 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(a)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 |