Getting ora-00923 error while exporting oracle sql data into csv format

Question

I'm trying to export oracle data into CSV format and the select statement has a column named 'type' with hardcoded value MSISDN. Note that there is no such column named type in any of the table but it is a column header that i need to include in CSV.

Below is my query , however i'm getting Ora-00923 From keyword not found where expected.

select
   /*+ parallel(i,24) parallel(m,24) */
   m.msisdn as "identifier" || ',' || 'MSISDN' as "type" ||','|| i.language || '_' || m.country 
from
    individual i,
    msisdn m 
where
   i.individual_id = m.individual_id 
   and i.account_status = '1' 
   and rownum < 11;

Where is the error in my query ?

Expected output:

identifier,type,locale
9321767493,MSISDN,en_IN
4421767493,MSISDN,en_GB
4921767493,MSISDN,de_DE

Answer 1

In the SELECT clause, concatenate all the values you need (including the commas etc), and write all column headings (as required) after AS.

Tables for testing:

create table individual ( id, status, language )
as
select 1000, '1', 'en' from dual union all
select 1001, '1', 'en' from dual union all
select 1002, '1', 'de' from dual union all
select 2000, '2', 'es' from dual union all
select 2001, '2', 'fr' from dual ;

create table msisdn ( individualid, msisdn, country )
as
select 1000, 9321767493, 'IN' from dual union all
select 1001, 4421767493, 'GB' from dual union all
select 1002, 4921767493, 'DE' from dual ;

Query

select
   m.msisdn || ',' || 'MSISDN' || ',' || i.language || '_' || m.country
   as "identifier,type,locale" 
from
    individual i,
    msisdn m 
where
   i.id = m.individualid 
   and i.status = '1' 
   and rownum < 11;

-- result
identifier,type,locale                               
-----------------------------------------------------
9321767493,MSISDN,en_IN                              
4421767493,MSISDN,en_GB                              
4921767493,MSISDN,de_DE 

On the command line (eg Oracle 18c, sqlcl) you can do the following:

SQL> set sqlformat csv
SQL> select
  2     m.msisdn || ',' || 'MSISDN' || ',' || i.language || '_' || m.country
  3     as "identifier,type,locale" 
  4  from
  5      individual i,
  6      msisdn m 
  7  where
  8     i.id = m.individualid 
  9     and i.status = '1' 
 10     and rownum < 11;

-- output
"identifier,type,locale"
"9321767493,MSISDN,en_IN"
"4421767493,MSISDN,en_GB"
"4921767493,MSISDN,de_DE" 

Also, you could/should use JOIN ... ON ... when writing inner joins eg

select
   m.msisdn || 'MSISDN' || i.language || '_' || m.country
   as "identifier,type,locale" 
from
   individual i join msisdn m on i.id = m.individualid 
where
       i.status = '1' 
   and rownum < 11;

DBfiddle here.