Oracle multiple column single Alias

Question

I am trying to select multiple columns and concatenate them in one column with a plus sign and one space on both sides of this plus sign in Oracle 11.2.0.1.0

SELECT ename AS Emplyee_name , deptno AS Department_Number , comm+" + " + sal AS This_Month_Comm_and_sal FROM emp WHERE ename = 'AHMAD';

I am getting this error

ORA-00904: " + ": invalid identifier

same is done in this w3schools tutorial W3schools SQL tutorial

how can I achieve the same result in ORACLE?

Don't use w3schools for reference that site is full of errors. Claiming that strings are concatenated in SQL using `+` is just one of them. — , Jan 20 '14 at 14:56
possible duplicate of [What is the string concatenation operator in Oracle?](http://stackoverflow.com/questions/278189/what-is-the-string-concatenation-operator-in-oracle) — Yaroslav Shabalin, Jan 20 '14 at 18:50

score 5 · Accepted Answer · edited Jan 20 '14 at 15:14

5

The string concatenation operator is || in Oracle: Single quotes must be used here.

SELECT 
    ename AS Emplyee_name , 
    deptno AS Department_Number , 
    TO_CHAR(comm) || ' + ' || TO_CHAR(sal) AS This_Month_Comm_and_sal 
FROM emp 
WHERE ename = 'AHMAD';

edited Jan 20 '14 at 15:14

Ahmadzkn

30
1
8

answered Jan 20 '14 at 14:51

D Stanley

149,601
11
178
240

I am Still getting the same error even after using || for concatenation. – Ahmadzkn Jan 20 '14 at 14:55
`||` is the string concatenation operator in *standard SQL* btw: there is no such thing as PSQL in Oracle. It's either PL/SQL - which this is not - or simply SQL. – Jan 20 '14 at 14:56
@Ahmadzkn If `comm` and `sal` are numeric types use `TO_CHAR` to convert to strings. – D Stanley Jan 20 '14 at 14:58
@DStanley 'TO_CHAR' did the trick also the double quotes had to be replaced with single quotes to achieve the desired result. Thank you so so much :) – Ahmadzkn Jan 20 '14 at 15:06

Oracle multiple column single Alias

1 Answers1