473,322 Members | 1,806 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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

Thanks in advance.

Adam Kovari
Jul 1 '08 #1
4 10595
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
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
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...@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
Thanks, already solved.

On 1. Júl, 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Joe | last post by:
Hi all Is there a TSQL function like Oracle's Translate function? Where: Translate('13,000 Miles','abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ, ',' ') would return '13000'? I'm...
1
by: David Furey | last post by:
Hi I have an XML documnet and a XSLT document as shown below THe XSLT document brings back a filtered docmument that has the VendorName that starts with a particular sub-string This works as...
1
by: kennethfine | last post by:
I'm transitioning from ASP development, please excuse these basic questions. One thing I did often in ASP was create a "translate" function to render one string to another, strip out excess...
6
by: bobueland | last post by:
The module string has a function called translate. I tried to find the source code for that function. In: C:\Python24\Lib there is one file called string.py I open it and it says
3
by: Mehmet Baserdem | last post by:
Hi all, I am trying to remove all blank chars in middle of the string "text1 text2 text3 text3 text4" with following stmt: VALUES(translate('text1 text2 text3 text3 text4' ...
4
by: Jo | last post by:
Which scalar function would be suitable for me to extract the date out of the following string: "060618154449" Thanks a lot for any advice.
1
by: =?Utf-8?B?R2F1cmF2?= | last post by:
Hi, I am using the Translate() function in one of the .XSLT file to remove the spaces, like this: <xsl:for-each select=".//Illustration"> <xsl:value-of select="translate(./@illusName, ' ',...
3
by: muybluie | last post by:
Hello All, I am trying something so simple, I have no idea why it doesn't work! create table test1 (c1 VARCHAR(16)); insert into test1 values( '12154846' ); select translate(c1, '5', 'A')...
4
by: Terry Reedy | last post by:
Cédric Lucantis wrote: 'ae' I do not claim this to be better than all the other methods, but this pair can also translate while deleting, which others cannot.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.