TRANSLATE function

[kovariadam]

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),
...
);

Thanks in advance.

Adam Kovari

[Tonkuma]

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.

[Lennart]

On Jul 1, 9:42 pm, "[e-mail]" <[e-mail]>
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,
'aaaaccdeeeiillnnoooorrsstuuuuyzzAAAACCDEEEIILLNNO OOORRSSTUUUUYZZ',
'áâãäæèïéìëíîåµñòóôöõàø¶¹»úüûùý¼¾ÁÂÃÄÆÈÏÉÌËÍÎÅ¥ÑÒÓ ÔÖÕÀئ©«ÚÜÛÙݬ®')
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

[kovariadam]

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 <[e-mail]:

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.

[kovariadam]

Thanks, already solved.

On 1. Júl, 22:20 h., Lennart <[e-mail]:

On Jul 1, 9:42 pm, "[e-mail]" <[e-mail]>
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,
'aaaaccdeeeiillnnoooorrsstuuuuyzzAAAACCDEEEIILLNNO OOORRSSTUUUUYZZ',
'áâãäæèïéìëíîåµñòóôöõàø¶¹»úüûùý¼¾ÁÂÃÄÆÈÏÉÌËÍÎÅ¥ÑÒÓ ÔÖÕÀئ©«ÚÜÛÙݬ®')
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