By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,127 Members | 1,366 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,127 IT Pros & Developers. It's quick & easy.

TRANSLATE function

P: n/a
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
Jul 1 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.
Jul 1 '08 #2

P: n/a
On Jul 1, 9:42 pm, "kovaria...@gmail.com" <kovaria...@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,
'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
Jul 1 '08 #3

P: n/a
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. Jl, 22:13 h., Tonkuma <tonk...@fiberbit.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.meno,
'aaaaccdeeeiillnnoooorrsstuuuuyzzAAAACCDEEEIILLNNO OOORRSSTUUUUYZZ',
'ť ئݬ')
FROM oa.pracovnik p;
----------------------------------------------------------

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

* 0 record(s) selected.
Jul 1 '08 #4

P: n/a
Thanks, already solved.

On 1. Jl, 22:20 h., Lennart <Erik.Lennart.Jons...@gmail.comwrote:
On Jul 1, 9:42 pm, "kovaria...@gmail.com" <kovaria...@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,
'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
Jul 1 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.