在预期的位置找不到FROM关键字，选择文本Oracle SQL [英] FROM keyword not found where expected, text selection Oracle SQL
问题描述
我想生成一个包含子字符串和另一个表ID的表，该子字符串出现在该表中.请参阅问题连接值
I want to produce a table which contains substrings and IDs of another table, where the substrings occur. See question Concatenate values.
create table table_expressions
(
A clob
);
insert all
into table_expressions (a) values ('atveroeosipsum')
into table_expressions (a) values ('test')
into table_expressions (a) values ('stetclitakasd')
into table_expressions (a) values ('noseatakimata')
into table_expressions (a) values ('loremipsumdolor')
into table_expressions (a) values ('consetetursadipscingelitr')
select * from dual;
create table a_x
(
A clob,
B clob
);
insert all
into a_x (a, b) values('atveroeosipsumloremipsumdolor', 1)
into a_x (a, b) values('stetclitakasdtest', 2)
into a_x (a, b) values('noseatakimataatveroeosipsum', 3)
into a_x (a, b) values('loremipsumdolor', 4)
into a_x (a, b) values('consetetursadipscingelitr', 5)
select * from dual;
create table a_y
as
with
input_strings ( a ) as (
select column_value from table_expressions
)
select t2.a, listagg(t1.b, ',') within group (order by t1.b)
as ids from a_x t1 join input_strings t2 on t1.a like '%' || t2.a || '%'
group by t2.a
table_expressions包含所需的子字符串
我的真实数据产生了很多要串联的ID.使用实际数据运行代码后，发生错误01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size..
The table_expressions contains the desired substrings
My real data produce quite a lot IDs to be concatenated. After running the code with the real data the error 01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size. occurs.
我如何重写代码以将生成的列IDs设置为CLOB格式?
我查看了问题 Listagg函数，但是我没有无法理解发布的答案中的代码.
How could I rewrite the code in order to format the resulting column IDs in CLOB format?
I looked at question Listagg function but I didn't understand the code in the posted answer.
此代码:
create table a_y
as
with
input_strings ( a ) as (
select a
from table_expressions
)
select t2.a, RTRIM(XMLAGG(XMLELEMENT(E,t1.a,',').EXTRACT('//text()') ORDER BY t1.a).GetClobVal(),',') AS LIST as ids
from a_x t1
join input_strings t2 on t1.a like '%' || t2.a || '%'
group by t2.a
;
产生错误FROM keyword not found where expected.
我想生成一个表a_y，它应该看起来像这样，列A ans IDs应该是clob格式:
produces error FROM keyword not found where expected.
I would like to produce a table a_y, which should look like that and columns A ans IDs should be in clob format:
A | IDs
-----------------------------|-------
atveroeosipsum | 1,3
test | 2
stetclitakasd | 2
noseatakimata | 3
loremipsumdolor | 1,4
consetetursadipscingelitr | 5
我该如何解决?
推荐答案
立即错误是由于为连接结果指定了两个别名引起的:您具有AS LIST as ids.您不能为计算结果指定两个别名.如果希望新创建的表的列为LIST，则删除as ids，反之亦然.
The immediate error is caused by having two aliases given to the result of the concatenation: You have AS LIST as ids. You can't give two aliases to the result of a calculation. If you want the newly created table to have a column LIST then delete as ids, and vice versa.
然后您将遇到另一个错误:您尝试在聚合中执行ORDER BY t1.a.那行不通；您不能通过XML聚合中的CLOB进行订购.您是否真的在乎聚合发生的顺序?如果不这样做，请更改为ORDER BY NULL.如果您确实关心的话，您会遇到问题，因为在Oracle中，order_by_clause根本无法通过CLOB表达式进行排序.您将必须使用其他方法来创建单独的列以进行订购.
Then you will run into another error: you are attempting to ORDER BY t1.a in the aggregation. That won't work; you can't order by a CLOB in the XML aggregation. Do you really care in what order the aggregation happens? If you don't, change to ORDER BY NULL. If you do care, you have a problem, since in Oracle an order_by_clause simply cannot order by a CLOB expression. You will have to create a separate column for ordering by using other methods.
在整个解决方案中，不需要WITH子句.在查询中引用"input_strings"的任何地方(WITH子句除外)，只需编写"table_expressions"即可.
In the solution overall, there is no need for the WITH clause. Wherever you refer to "input_strings" in the query (other than the WITH clause), simply write "table_expressions".
编辑
这是如何使其工作的方法.首先，我将显示CREATE TABLE语句.我将假定table_expressions具有搜索字符串的CLOB列，并且此列中没有重复项.即使这样，该表也需要一个单独的主键，该主键的数据类型不是LOB或其他长的非标准类型.我为此使用NUMBER.
Here is how this could be made to work. First I will show the CREATE TABLE statements. I will assume that table_expressions has a CLOB column of search strings, and that there are NO DUPLICATES in this column. Even so, the table also needs a separate primary key, of a data type that is not LOB or other long, not-standard-type. I use NUMBER for this.
然后我通过此主键列进行汇总. las，我无法同时选择搜索字符串.我可以SELECT MAX(t2.a)，但这也不适用于CLOB值！相反，我需要进一步的联接以将主键与搜索字符串匹配. (很抱歉，由于这个原因，查询将花费更长的时间...)
Then I aggregate by this primary key column. Alas, I can't select the search string at the same time. I could SELECT MAX(t2.a) but that doesn't work with CLOB values either! Instead, I need a further join to match the primary key to the search string. (Sorry, the query will take that much longer because of this...)
在聚合中，我按列a中字符串值的前4000个字符进行排序.这不如按整个输入字符串排序，但仍比按NULL排序要好.
In the aggregation, I sort by the first 4000 characters of the string value from column a. This is not as good as sorting by the entire input string, but it is still better than ordering by NULL.
create table a_x ( a, b ) as
select to_clob('atveroeosipsumloremipsumdolor'), 1 from dual union all
select to_clob('stetclitakasdtest') , 2 from dual union all
select to_clob('noseatakimataatveroeosipsum') , 3 from dual union all
select to_clob('loremipsumdolor') , 4 from dual union all
select to_clob('consetetursadipscingelitr') , 5 from dual
;
create table table_expressions ( a, pk ) as
select to_clob('atveroeosipsum') , 10 from dual union all
select to_clob('test') , 11 from dual union all
select to_clob('stetclitakasd') , 12 from dual union all
select to_clob('noseatakimata') , 13 from dual union all
select to_clob('loremipsumdolor') , 14 from dual union all
select to_clob('consetetursadipscingelitr'), 15 from dual
;
create table a_y as
select te.a, s.ids
from table_expressions te
join
(select t2.pk, RTRIM(XMLAGG(XMLELEMENT(E,t1.a,',').EXTRACT('//text()')
ORDER BY cast(t1.a as varchar2(4000))).GetClobVal(),',') as ids
from a_x t1
join table_expressions t2
on t1.a like '%' || t2.a || '%'
group by t2.pk
) s
on te.pk = s.pk
;
现在让我们检查一下我们得到了什么:
Now let's check what we got:
select * from a_y;
A IDS
------------------------- ---------------------------------------------------------
atveroeosipsum atveroeosipsumloremipsumdolor,noseatakimataatveroeosipsum
test stetclitakasdtest
stetclitakasd stetclitakasdtest
noseatakimata noseatakimataatveroeosipsum
loremipsumdolor atveroeosipsumloremipsumdolor,loremipsumdolor
consetetursadipscingelitr consetetursadipscingelitr
编辑#2
如果您需要连接表a_x(列b)中的ID，而不是CLOB本身，则将t1.a替换为t1.b(并且在XMLAGG的ORDER BY子句中，您不需要任何cast，只需order by t1.b).
If you need to concatenate the id's from table a_x (column b), not the CLOBs themselves, then replace t1.a with t1.b (and, in the ORDER BY clause of XMLAGG, you don't need any cast, just order by t1.b).
drop table a_y purge;
create table a_y as
select te.a, s.ids
from table_expressions te
join
(select t2.pk, RTRIM(XMLAGG(XMLELEMENT(E,t1.b,',').EXTRACT('//text()')
ORDER BY t1.b).GetClobVal(),',') as ids
from a_x t1
join table_expressions t2
on t1.a like '%' || t2.a || '%'
group by t2.pk
) s
on te.pk = s.pk
;
select * from a_y;
A IDS
------------------------- ---
atveroeosipsum 1,3
test 2
stetclitakasd 2
noseatakimata 3
loremipsumdolor 1,4
consetetursadipscingelitr 5
这篇关于在预期的位置找不到FROM关键字，选择文本Oracle SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
