IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Comparing Decimal and CHAR columns


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 61
Location: San Diego

PostPosted: Thu Oct 20, 2016 2:33 am
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: 2481
Location: @my desk

PostPosted: Thu Oct 20, 2016 3:03 am
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2588
Location: NYC,USA

PostPosted: Thu Oct 20, 2016 3:10 am
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: 61
Location: San Diego

PostPosted: Thu Oct 20, 2016 7:46 am
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2588
Location: NYC,USA

PostPosted: Thu Oct 20, 2016 12:08 pm
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

Senior Moderator


Joined: 14 Mar 2007
Posts: 10727
Location: italy

PostPosted: Thu Oct 20, 2016 3:09 pm
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: 67
Location: Lower Saxony (DE)

PostPosted: Thu Oct 20, 2016 4:11 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2588
Location: NYC,USA

PostPosted: Thu Oct 20, 2016 6:11 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts HOW TO OVERLAY A Packed decimal VALUE... DFSORT/ICETOOL 5
No new posts COnvert a column with mix of hex ,cha... DB2 5
No new posts How do i verify the char field is hav... PL/I & Assembler 1
This topic is locked: you cannot edit posts or make replies. Parsing more than 1000 columns in a s... SYNCSORT 10
No new posts To convert numeric to Packed Decimal DFSORT/ICETOOL 9
Search our Forums:

Back to Top