|
View previous topic :: View next topic
|
| Author |
Message |
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
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 |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| post the sql you are trying to execute along with the sample data? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
| Back to top |
|
|
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
when I see questions/code snippets like this one I wonder about the quality of the application and the relative database design  |
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| 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 |
|
|
|
|
