coalesce mybatis switch case

Question

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

Is it MySQL or Oracle? I removed the inconsistent tags. Please tag the specific database. — The Impaler, Jul 24 '20 at 20:13

ScaisEdge · Accepted Answer · 2020-08-10T05:38:41.103

0

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>

edited Aug 10 '20 at 05:38

answered Jul 24 '20 at 19:37

ScaisEdge

131,976
10
91
107

Thanks for the response.Getting like this again The error occurred while setting parameters.Cause: java.sql.SQLException: ORA-01427: single-row subquery returns more than one row – Elakkiya Jul 24 '20 at 19:59
@Elakkiya This query does not have subqueries in it. Are you sure this is the exact query you are running? – The Impaler Jul 24 '20 at 20:15
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 – Elakkiya Jul 24 '20 at 20:37
@Elakkiya .. is evident that you are not using my code ..answer updated . with your code refactored .. – ScaisEdge Jul 25 '20 at 05:33
@scaisEdge thank u so much, got resolved after adding rownum=1 – Elakkiya Jul 27 '20 at 14:53
I have repaired a large number of typos and spelling errors in this post; was this written on a mobile device? – halfer Aug 09 '20 at 22:45
What does "c this raise the error" mean? I could not make any sense of that, and thus could not repair it. – halfer Aug 09 '20 at 22:45

score 0 · Answer 2 · answered Jul 24 '20 at 21:08

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.

Thank you so much. Got the value after added the rownum =1 in both subquery and main query — Elakkiya, Jul 27 '20 at 14:47

coalesce mybatis switch case

2 Answers2