AFAIK, concatenation of strings and numbers is performed using the || operator or CONCAT() function. So executing select '5.00' || 1 from dual; should return 5.001.
Executing select 5.00 || 1 from dual; will give you 51.
If you want to add one number to the other, you need to drop the single quotes: select 5.00 + 1 as col1 from dual; → returns 6.
Hi - The query runs just fine for me on MySQL.
What database & SQL dialect do you use?
What is the error message?
Hi , database Oracle and SQL dialect <Oracle, Oracle SQL Plus>
Error msg : [42000][1722] ORA-01722: invalid number
What is the expected result of the query?
AFAIK, concatenation of strings and numbers is performed using the
||operator orCONCAT()function. So executingselect '5.00' || 1 from dual;should return5.001.Executing
select 5.00 || 1 from dual;will give you51.If you want to add one number to the other, you need to drop the single quotes:
select 5.00 + 1 as col1 from dual;→ returns6.yes, the problem is on Datagrip version window this query can run, but on mac version it can not run and i dont know why
Please collect IDE logs and upload them to https://uploads.jetbrains.com for analysis:
Don't forget to provide the id of the upload here.
SQL log in :
-- 2024-01-12 07:37:06 [Burger] [PSP TEST] [console: console_1 [PSP TEST]] [session id: 1065477147] [statement id: 1619840272]
select '5.00' + 1 from dual
-- ] --------------------------------------------------
-- 2024-01-12 07:37:06 [Burger] [PSP TEST] [console: console_1 [PSP TEST]] [session id: 1065477147] [statement id: 1619840272] error - [42000][1722] ORA-01722: invalid number
-- 2024-01-12 07:37:06 [Burger] [PSP TEST] [console: console_1 [PSP TEST]] [session id: 1065477147] [statement id: 1619840272] finished - execution time: 103 ms
Hello,
Do you use the same DB on Mac and Windows and driver versions? Could you please provide log from Windows machine where it works fine?
Yes, i use the same DB and driver versions in mac and window
this is log in window version:
-- 2024-01-15 10:38:46 [Burger] [DB TEST] [console: console_1 [DB TEST]] [session id: 1039362261] [statement id: 1756569505]
select '5.00' + 1 from DUAL
-- ] --------------------------------------------------
-- 2024-01-15 10:38:46 [Burger] [DB TEST] [console: console_1 [DB TEST]] [session id: 1039362261] [statement id: 1756569505] results received
-- 2024-01-15 10:38:46 [Burger] [DB TEST] [console: console_1 [DB TEST]] [session id: 1039362261] [statement id: 1756569505] finished - execution time: 40 ms, fetching time: 86 ms, total result sets count: 1
Seems you have different decimal separator on machines. You may run this query to check current settings:
"select value
from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS';"
To avoid such cases you may use “to_number” function:
“select to_number('5.00', '99999D99','NLS_NUMERIC_CHARACTERS=''.,''') + 1 from DUAL;”
or run this before request:
“alter session set NLS_NUMERIC_CHARACTERS = '.,';”