473,383 Members | 1,885 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,383 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 10608
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. 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
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 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:
Cdric 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.
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.