Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Comparing Decimal and CHAR columns

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 59
Location: Atlanta

PostPosted: Thu Oct 20, 2016 2:33 am    Post subject: Comparing Decimal and CHAR columns
Reply with quote

Hello,

I am trying to create a query which will fetch me rows which has matching values between 2 columns. Column 1 is declared as DECIMAL(17) and Column 2 is CHAR 15. I tried CAST and SUBSTR but end up with -104. Let me know how to approach this problem.

Thanks
R
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Thu Oct 20, 2016 3:03 am    Post subject:
Reply with quote

post the sql you are trying to execute along with the sample data?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Thu Oct 20, 2016 3:10 am    Post subject:
Reply with quote

Learn SQL Syntax
Code:
col1=CAST(col2 AS dec(17))
Back to top
View user's profile Send private message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 59
Location: Atlanta

PostPosted: Thu Oct 20, 2016 7:46 am    Post subject:
Reply with quote

I get back

sqlcode: -420

sqlstate: 22018
Code:

SQL0420N  Invalid character found in a character string argument of the
function "DECIMAL".  SQLSTATE=22018


Code:
SQL0420N  Invalid character found in a character string argument of the function "DECIMAL


SQL used:
Code:

select * from ABCitm where COl1=CAST(COL2 as dec(17)) with ur;


Code:
COL1  COL2
123         123
31231    21312
345          345
53453    FEF313


result

Code:
COL1 COL2
123   123
345   345


Code'd for you
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Thu Oct 20, 2016 12:08 pm    Post subject:
Reply with quote

Of course you will get the sqlcode -420.
Why are you comparing alphanumeric to decimal? This is wrong at first place and still if you want to go ahead then you should either skip the one with alphanumeric or remove alphabets from the alphanumeric part and then compare. You need to explain better what do you want to achieve to get moving further.
Please learn to use code tags.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Oct 20, 2016 3:09 pm    Post subject: Reply to: Comparing Decimal and CHAR columns
Reply with quote

when I see questions/code snippets like this one I wonder about the quality of the application and the relative database design icon_surprised.gif
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 40
Location: Lower Saxony (DE)

PostPosted: Thu Oct 20, 2016 4:11 pm    Post subject:
Reply with quote

try the other direction, try to convert numeric values in alphanumerics:
Use CAST(col1 AS VARCHAR(17)) (i.e. 123 would be converted in '123') or DIGITS(col1) (i.e. 123 would be converted in '0000000123' or something similar)...
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Thu Oct 20, 2016 6:11 pm    Post subject:
Reply with quote

Even if you try other direction, how will you get a match? Because fixing -420 is easy but it's useless to compare apple vs oranges.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts comparing comp-3 and unpacked numbers juares castro COBOL Programming 3 Mon May 30, 2016 6:46 pm
No new posts Convert Decimal to hexadecimal Steve Ironmonger DFSORT/ICETOOL 5 Fri May 27, 2016 4:19 pm
This topic is locked: you cannot edit posts or make replies. JCL to convert float decimal into rea... polymathtarun JCL & VSAM 3 Sun May 15, 2016 9:57 pm
No new posts Need help on comparing hexadecimal va... ashok_uddaraju DFSORT/ICETOOL 4 Thu Apr 28, 2016 2:08 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us