Categories

Main
Database
Java
Microsoft.NET
Adabas
DB2
Informix
Microsoft SQL Server
MySQL
Oracle
Pervasive.SQL
PostgreSQL
Sybase
Other
ASP
ColdFusion
Crystal Reports
Delphi, C etc
JAVA
Microsoft.NET
Perl and the DBI
PHP
ANSI SQL
Unix Shell Scripts
Visual Basic
XML & XSLT
Corel Paradox
FileMaker
Microsoft Access
Microsoft Excel
Other PC Databases
Applications & Tools
Database Concepts & Design
EJB programming & troubleshooting
EJB design
General J2EE
XML & Web services
Web tier: servlets, JSP, Web frameworks
Performance and scalability
Industry news
TSS feedback
Mobicents Contributors
Mobicents Users
JSLEE Resource Adaptor Types
Planning JavaOne 2008
Sun Tech Days
Other Java conferences
Binary Web Services and XML
Metro and JAXB
GlassFish
GlassFish Plugins
Project jMaki
GlassFish WebTier
Mural
Java Development Tools
Java WS & XML Community News
JAXP
Java SE
6uN Early Access
Java Quick Starter
Java SE Snapshots: Project Feedback
JCK Forum
Feedback and Suggestions
JDK Distros
JDK Open Source
General JSR Discussion
JCP 2.6
JXTA Community Forum
ME Interest
ME Cool Apps
ME General Help
ME Feedback & Suggestions
ME Application Developer Interest
Blu-ray Disc Java
ME Developer Days
Squawk
Mobile Developer Alliance
OpenCable
LWUIT
JavaFX Script Language Discussion
OpenJFX General Discussion
Scene Graph
General Performance Discussion
Your Java Career
NetBeans 6.0
Servlets
JSP
JSF
Portals and Portlets
EJB and Other Java EE Technologies
Distributed Java
Object Relational Mapping
JDBC
Web Services
Swing / AWT / SWT / JFace
JNLP and Web Start
Java Micro Edition
Sockets and Internet Protocols
Threads and Synchronization
Performance
Applets
I/O and Streams
Other Java APIs
Game Development
Java in General (beginner)
Java in General (intermediate)
Java in General (advanced)
Programmer Certification (SCJP)
Developer Certification (SCJD)
Associate Certification (SCJA)
Web Component Certification (SCWCD)
EJB Certification (SCBCD)
Mobile Application Certification (SCMAD)
Architect Certification (SCEA)
Web Services Certification (SCDJWS)
XML Certification
Product and Other Certifications
Mock Exam Errata
Sun Certification Results
Authors' Corral
Book Reviews
Events
Bunkhouse Porch
Teachers' Lounge
Testing
OO, Patterns, UML and Refactoring
IDEs, Version Control and other tools
Ant, Maven and Other Build Tools
Linux / UNIX
Mac OS
HTML and JavaScript
XML and Related Technologies
Agile and Other Processes
General Computing
Security
Groovy
Scala
Other Languages
Struts
Application Frameworks
Other Open Source Projects
BEA/Weblogic
IBM/Websphere
Oracle/OAS
Apache/Tomcat
JBoss
Other Java Products and Servers
JavaRanch
Cattle Drive (java college)
Moderators Only
Trash Can
Jobs Offered
Jobs Wanted
Jobs Discussion
Meaningless Drivel
Programming Diversions
Blatant Advertising
Java Announcements
New To Java
Advanced Java
Java Applets
Networking
Threads and Synchronization
Java 2D
AWT / Swing
SWT / JFace
CLDC and MIDP
CDC and Personal Profile
Sun Java Wireless Toolkit
Enterprise JavaBeans
JavaServer Pages (JSP) and JSTL
Java Servlet
JavaServer Faces
Web Frameworks
Database
XML
Lucene
NetBeans
Eclipse
IntelliJ IDEA
JCreator
Other IDEs
Java Tutorials
Java Tips
Jobs Discussion
Jobs Offered
Jobs Wanted
Professional Certification
Forum Lobby
Java Blogs
Introductions
Reviews / Advertising
Suggestions & Feedback

Resources

Java Database
Linux
Coding
Mobile
Hardware
Software Development
Software Development
iOS,OS X
iOS,OS X
ORACLE
IBM DEVELOPER
IBM DEVELOPER
MSDN
MSDN


Tags

DB2

DB2 is IBM's offering to the highend database market. The latest version of DB2 (Universal Database) is ideal for OLTP, Data Warehousing, Decision Support and everything in between. It's well priced, extremely scalable and runs on virtually every platform out there from handhelds to mainframes.

Weird CASE behaviour (SQL0420 - character in CAST argument not valid)


Hi,

I'm new to DB2 and I ran into the following problem trying to convert some (Oracle) DECODE functions to CASE:

Consider this statement:

update HERONDERZ_5412 CCT set
OPDRCODE_OMS=(select substr(OMS,1,24) from KODERINGEN_5410
where SRTTAB= CASE CCT.OPDRCAT 
when 1 then 12
when 2 then 13
when 3 then 14 
when 4 then 74
when 5 then 77
else 0
end
and dec(CODE)=CCT.OPDRCODE)

Results in a "SQL0420 - Character in CAST argument not valid"

In my test situation the table being updated only contains the value 2 for CCT.OPDRCAT.

So I tried the following statement:

update HERONDERZ_5412 CCT set
OPDRCODE_OMS=(select substr(OMS,1,24) from KODERINGEN_5410
where SRTTAB=13
and dec(CODE)=CCT.OPDRCODE)

And (surprisingly enough) this works just fine.

Each of the (relevant) columns have the following datatypes:

SRTTAB NUMERIC(4)
CODE VARCHAR(10)
OPDRCAT NUMERIC(1)
OPDRCODE NUMERIC(2)

And I'm using DB2 UDB for iSeries V5R1, by the way.

If anyone could explain what's causing the error it'd be greatly appreciated.

TIA.

Roelof

Just a guess (I'm not familiar with your version) ...

OPDRCAT NUMERIC(1)

If the size (1) refers to the length and not the byte size, could it not be falling over attempting to cast an int of length 1 to an int of length 2?

I don't know..

I tried the same query on our Oracle(9i) server, and it works just fine (provided I replace the dec() funtion by to_number()).

This leads me to believe that the statement is syntacticly correct, but that something inside DB2's 'black box' is messing things up.

I ran the statement over an ODBC connection to our AS/400 server and only got the SQL0420 error.

When I log on the the server and run the statement from the commandline I get this:

 CPD4002:  Key mapping error in field *N member KODER00001.      
 CPD4002:  Key mapping error in field *N member KODER00001.      
 ...
 < the above about 30 times,  followed by : >
 SQL0420:  Character in CAST argument not valid.                 

Maybe this rings a bell with someone, but to me it might just as well have been written in Swahili.

Any suggestions on how to fix this problem (or work around it) would be appreciated.

Thanks.

Roelof

Well, I managed to find a solution. So, in case someone runs into a similar problem:

update HERONDERZ_5412 CCT set
OPDRCODE_OMS=(select substr(OMS,1,24) from KODERINGEN_5410
where dec(CODE)=CCT.OPDRCODE
and SRTTAB=cast(CASE CCT.OPDRCAT 
   when  1 then 12
   when  2 then 13
   when  3 then 14
   when  4 then 74
   when  5 then 77
   else 0
end as numeric(2)))

^-  this works.

Why I have to cast a numeric value to a numeric value, I have no idea (and it seems rather pointless to me), but this solved my problem.

Still, if anyone can clear up this behaviour, then by all means..


Related Links

Unable to contact DB2 through Client Machine
Assigning sequence value to a variable
Termination Characters
Join with Sum
Backup Policy
urgent help
"0xFFFFD121"
How to delete queued task in task center
DB2 7.2 on server and DB2 connect 8.1 on client ! !
Transaction rollbacks
db2dart..
unable to start the db2 using db2start
Help required!
indexing documents with datalink datatype
Order of records...
java connect error