coalesce mybatis switch case

huangapple go评论71阅读模式
英文:

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

&lt;select id=&quot;Trigger&quot; parameterType=&quot;hashmap&quot; resultType=&quot;java.util.HashMap&quot; flushCache=&quot;true&quot;&gt; 

	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 &quot;ProgressDate&quot;
		, table3.id as &quot;ID&quot; 
		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} 
		
&lt;/select&gt; 

答案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 &quot;ProgressDate&quot;, 
  table3.id as &quot;ID&quot; 
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 &quot;ProgressDate&quot;, 
  table3.id as &quot;ID&quot; 
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.

huangapple
  • 本文由 发表于 2020年7月25日 03:30:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/63080328.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定