英文:
coalesce mybatis switch case
问题
我在我的查询中使用了 coalesce 函数和 MyBatis 开关语句,但是出现了错误,错误信息如下:
> 查询数据库时出错。原因:java.sql.SQLException: ORA-01427:单行子查询返回多于一行。
这是我的查询语句:
(select
(case when (coalesce(t1.col1,t2.col1, t1.col2, t1.col3) is null)
then (select sysdate from dual)
else (coalesce(t1.col1,t2.col1, t1.col2, t1.col3))
end )
from table1 t1
join table2 t2
on t1.id IN (t2.id))
提前感谢您的帮助。
英文:
Iam using coalesce mybatis switch case in my query, where iam getting error like
> Error querying database. Cause: java.sql.SQLException: ORA-01427:
> single-row subquery returns more than one row
this is my query
(select
(case when (coalesce(t1.col1,t2.col1, t1.col2, t1.col3) is null)
then (select sysdate from dual)
else (coalesce(t1.col1,t2.col1, t1.col2, t1.col3))
end )
from table1 t1
join table2 t2
on t1.id IN (t2.id))
Thanks in advance
答案1
得分: 0
似乎你有很多 () 但总体上你应该使用 = 操作符而不是 IN (t2.id) 来连接 t2.id。
SELECT
CASE WHEN COALESCE(t1.col1, t2.col1, t1.col2, t1.col3) IS NULL
THEN SYSDATE
ELSE COALESCE(t1.col1, t2.col1, t1.col2, t1.col3)
END
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
并且从你发布的代码中可以看出,你在一个列结果中有一个 SELECT,并且这个 SELECT 返回多行(这会引发错误)。你还在连接语法上混合了一些基于显式连接语法的部分,以及一些基于旧的隐式连接语法,使用逗号分隔的表名和 WHERE 条件。你应该尝试使用以下代码:
<select id="Trigger" parameterType="hashmap" resultType="java.util.HashMap" flushCache="true">
SELECT
CASE WHEN COALESCE(table1.col1, table2.col2, table1.col3, table1.col4) IS NULL
THEN SYSDATE
ELSE COALESCE(table1.col1, table2.col2, table1.col3, table1.col4)
END AS "ProgressDate",
table3.id AS "ID"
FROM table1
INNER JOIN table2 ON table1.id = table2.id
INNER JOIN table3 ON table1.id = table3.id
INNER JOIN table4 ON table2.action = table4.action
WHERE table3.transaction = #{inputvaluepassed}
</select>
英文:
Seems you have a lot of () but overall you should use = operator and not IN (t2.id) for join t2.id
select
case when coalesce(t1.col1,t2.col1, t1.col2, t1.col3) is null
then sysdate
else coalesce(t1.col1,t2.col1, t1.col2, t1.col3)
end
from table1 t1
join table2 t2 on t1.id = t2.id
And looking at the code you posted in sample you have a select as a column result and this select return several rows, ( this raise the error). You also have a mixin of join syntax some based on explicit join syntax some based on old implicit join syntax based on comma separated table name and where condition. You should try using this
<select id="Trigger" parameterType="hashmap" resultType="java.util.HashMap" flushCache="true">
SELECT
select case when coalesce(table1.col1, table2.col2,table1.col3, table1.col4) is null
then sysdate
else coalesce(table1.col1, table2.col2,table1.col3, table1.col4) end as "ProgressDate"
, table3.id as "ID"
from table1
INNER join table2 on table1.id = table2.id
INNER JOIN table3 ON table1.id = table3.id
INNER JOIN table4 table2.action = table4.action
WHERE table3.transaction = #{inputvaluepassed}
</select>
答案2
得分: 0
你在问题中提到的查询代替了另一个主查询中的标量子查询。我已经对整个查询进行了格式化(以提高可读性),如下所示:
SELECT
(
select case when coalesce(table1.col1, table2.col2,table1.col3,
table1.col4) is null
then (select sysdate from dual)
else coalesce(table1.col1, table2.col2,table1.col3, table1.col4)
end
from table1
join table2 on table1.id = table2.id
) as "ProgressDate",
table3.id as "ID"
FROM table3, table1, table2, table4
WHERE table3.transaction = #{inputvaluepassed}
AND table1.id = table3.id
AND table2.id=table1.id and table2.action = table4.action
根据定义,标量子查询只能返回零行或一行。在你的情况下,似乎在运行时,这个子查询返回了多行,导致主查询崩溃。
你需要想办法最多生成一行结果:可以通过对行进行聚合(使用GROUP BY
),也可以从结果集中选择一行(使用LIMIT
);还有其他选项。如果我们选择将行数限制为最多1行,你的查询可能如下所示:
SELECT
(
select case when coalesce(table1.col1, table2.col2,table1.col3,
table1.col4) is null
then (select sysdate from dual)
else coalesce(table1.col1, table2.col2,table1.col3, table1.col4)
end
from table1
join table2 on table1.id = table2.id
limit 1 -- 添加了这行
) as "ProgressDate",
table3.id as "ID"
FROM table3, table1, table2, table4
WHERE table3.transaction = #{inputvaluepassed}
AND table1.id = table3.id
AND table2.id=table1.id and table2.action = table4.action
这只是解决此问题的一种可能的简单解决方案。对如何从多个行中选择正确的行有更好的理解可能会产生更好的解决方案。
英文:
The query you mention in the question takes the place of a scalar subquery included in another... main query. I formatted the whole query (for readability) and it looks like this:
SELECT
(
select case when coalesce(table1.col1, table2.col2,table1.col3,
table1.col4) is null
then (select sysdate from dual)
else coalesce(table1.col1, table2.col2,table1.col3, table1.col4)
end
from table1
join table2 on table1.id = table2.id
) as "ProgressDate",
table3.id as "ID"
FROM table3, table1, table2, table4
WHERE table3.transaction = #{inputvaluepassed}
AND table1.id = table3.id
AND table2.id=table1.id and table2.action = table4.action
Now, by definition, scalar subqueries can only return zero or one row. In your case it seems that at runtime this subquery is returning multiple rows, and the main query crashes.
You'll need to somehow produce a single row at most: maybe by aggregating the rows (using GROUP BY
), maybe by picking one row only from the result set (using LIMIT
); there are other options. If we choose the to limit the rows to 1 at most your query could look like:
SELECT
(
select case when coalesce(table1.col1, table2.col2,table1.col3,
table1.col4) is null
then (select sysdate from dual)
else coalesce(table1.col1, table2.col2,table1.col3, table1.col4)
end
from table1
join table2 on table1.id = table2.id
limit 1 -- added this line
) as "ProgressDate",
table3.id as "ID"
FROM table3, table1, table2, table4
WHERE table3.transaction = #{inputvaluepassed}
AND table1.id = table3.id
AND table2.id=table1.id and table2.action = table4.action
This is just one possible cheap solution to the issue. A better understanding on how to pick the right row over multiples ones can produce a better solution.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论