我有一个表(表的描述):
Name | Type |
---|---|
KEYVALUE | VARCHAR2(100) |
TEXT | CLOB |
示例
Keyvalue | Text |
---|---|
101 | Customer Input 05/15/2023 07:20:20 My name is ABX +++ Private Notes What is you name+++Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23 +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English |
102 | Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket+++Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes or maintenance performed? - Language Preference: English |
我基本上是用"客户输入"一词将整个阶段分成多行. 类似于以下内容:
SELECT distinct keyvalue, level pos, trim(regexp_substr(text, 'Customer Input[^+++]*', 1, level)) x
FROM
(
SELECT 101 as keyvalue,'Customer Input 05/15/2023 07:20:20 My name is ABX +++ Private Notes What is you name+++Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23
+++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English| '
as text from dual
union all
SELECT 102 as keyvalue,' Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket+++Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one
+++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes
or maintenance performed? - Language Preference: English| '
as text from dual
) t
CONNECT BY instr(text, 'Customer Input', 1, level - 1) > 0
order by keyvalue;
keyvalue | pos | text |
---|---|---|
101 | 1 | Customer Input 05/15/2023 07:20:20 My name is ABX |
101 | 2 | Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23 |
101 | 3 | Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English |
101 | 4 | |
102 | 1 | Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket |
102 | 2 | Customer Input 04/30/2023 19:40:58 Please replace the item as this is a faulty one |
102 | 3 | Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes or maintenance performed? - Language Preference: English |
102 | 4 |
这可以很好地工作,因为文本列是CHARACTER数据类型.
但是当我运行下面的查询时(在数据类型为lob的Actual列上)
SELECT distinct keyvalue, level pos, trim(regexp_substr(customer_input_info, 'Customer Input[^+++]*', 1, level)) str
FROM (select 101 as keyvalue,to_clob('Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket+++Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one
+++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes
or maintenance performed? - Language Preference: English| ') as customer_input_info from dual) t
CONNECT BY instr(customer_input_info, 'Customer Input', 1, level - 1) > 0
order by 1
我的错误越来越小了
ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 44 Column: 38.
我无法更改内部SQL查询,因为源表是CLOB数据类型.我应该对外部查询进行哪些更改.