TRANSLATE function

Originally Posted by

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,
'aaaaccdeeeiillnnoooorrsstuuuuyzzAAAACCDEEEIILLNNO OOORRSSTUUUUYZZ',
'áâãäæèïéìëíîåµñòóôöõàø¶¹»úüûùý¼¾ÁÂÃÄÆÈÏÉÌËÍÎÅ¥ÑÒÓ ÔÖÕÀئ©«ÚÜÛÙݬ®')
FROM oa.pracovnik p;
>
---
CREATE TABLE oa.pracovnik (
...
meno CHARACTER(20),
...
);
>

Originally Posted by

On Jul 1, 9:42 pm, "kovaria...@gmail.com" <kovaria...@gmail.com>
wrote:
>
>
>
>
>

Quote: Originally Posted by 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

>

Quote: Originally Posted by with this query: SELECT TRANSLATE(p.meno, 'aaaaccdeeeiillnnoooorrsstuuuuyzzAAAACCDEEEIILLNNO OOORRSSTUUUUYZZ', 'áâãäæèïéìëíîåµñòóôöõàø¶¹»úüûùý¼¾ÁÂÃÄÆÈÏÉÌËÍÎÅ¥ÑÒÓ ÔÖÕÀئ©«ÚÜÛÙݬ®') FROM oa.pracovnik p;

>

Quote: Originally Posted by --- 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

Originally Posted by

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,
'aaaaccdeeeiillnnoooorrsstuuuuyzzAAAACCDEEEIILLNNO OOORRSSTUUUUYZZ',
'áâãäæèïéìëíîåµñòóôöõàø¶¹»úüûùý¼¾ÁÂÃÄÆÈÏÉÌËÍÎÅ¥ÑÒÓ ÔÖÕÀئ©«ÚÜÛÙݬ®')
FROM oa.pracovnik p;
----------------------------------------------------------
>
1
--------------------
>
* 0 record(s) selected.