DB version: 11.2.0.4
Query1 runs fine. In Query2 , I just want the values to be separated using the pipe operator ( '|' ) .
But Query2 errors out with "ORA-01722: invalid number" . When I enclose the CREATED+1/3 column in parenthesis, the issue is fixed.
What is the root cause of this issue?
--Query1
select
X_ORDER_SUB_TYPE ,
CREATED+1/3
from SIEBEL.S_ORDER
WHERE ROWNUM < 5;
X_ORDER_SUB_TYPE CREATED+1
----------------------------------- ---------
Change SIM 19-SEP-17
Change SIM 19-SEP-17
New Registration 19-SEP-17
Change Rate Plan 19-SEP-17
--- Query2
SQL> select
X_ORDER_SUB_TYPE || '|' ||
CREATED+1/3
from SIEBEL.S_ORDER
WHERE ROWNUM < 5; 2 3 4 5
X_ORDER_SUB_TYPE || '|' ||
*
ERROR at line 2:
ORA-01722: invalid number
--- Fix : Enclose the CREATED+1/3 column in paranthesis
select
X_ORDER_SUB_TYPE || '|' || (CREATED+1/3)
from SIEBEL.S_ORDER
WHERE ROWNUM < 5;
X_ORDER_SUB_TYPE||'|'||(CREATED+1/3)
--------------------------------------------------------------------------------
Change SIM|19-SEP-17
Change SIM|19-SEP-17
New Registration|19-SEP-17
Change Rate Plan|19-SEP-17