Hardcode value of column name and column in select query with pipe delimiter

Question

I want to hard-code a column name and its value as New York in a select query with pipe delimiter. E.g. Emp table has columns EmpId, EmpName, Salary. I want output such as

Select EmpId ||'|'||
       EmpName ||'|'||
       'NewYork' as City  ||'|'||
       Salary
 from Emp

Here I want City column in output query and its value should be 'NewYork' for each record.

Here I am getting error as "FROM keyword not found where expected". When I use comma instead of Pipe Delimiter I am getting result but not with Pipe. Please advise. Thanks in advance.

Answer 1

with emps as (
  select 1 as id, 'Smith' as name, 2000 as salary from dual
  union
  select 2, 'Jones', 2200 from dual
)
select
  id || '|' || name as record1,
  id || '|' || name || '|NewYork|' || salary as record2,
  'NewYork' as city
from emps;