将oracle sql数据导出为csv格式时出现ora-00923错误

Question

我正试图将oracle数据导出为CSV格式，select语句有一个名为"type"的列，其硬编码值为MSISDN。请注意，在任何表中都没有这样的列命名类型，但它是我需要包含在CSV中的列标题。

下面是我的查询，但是我得到了Ora-00923From关键字，在预期的地方找不到。

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;

我的查询中的错误在哪里？

预期输出：

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

Answer 1

在SELECT子句中，连接所有需要的值(包括逗号等(，并在as.之后写入所有列标题(根据需要(

测试表：

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 ;

查询

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 

在命令行(例如Oracle 18c、sqlcl(上，您可以执行以下操作：

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" 

此外，您可以/应该使用JOIN。。。在。。。写入内部联接时(如(

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;

D小提琴在这儿。