Query works in SQL Developer but has error in MyBatis

Question

My query runs fine in SQL Developer, but when I copy the same query into the mapper XML file for MyBatis, I get an ORA-00900: invalid SQL statement.

Subset of mapper file:

    <select id="getTotalUniqueUsersByPage">
            resultType="KeyValuePair" 
            parameterType="RequestFilter">
        select KEY as key,sum(single_value) as value from (
            select schcm.title as key
            ,schuc.usage_count AS single_value 
            from usage_count schuc
            inner join content_master schcm
            on schcm.trace_id=schuc.trace_key
            where schcm.pagetype='Page'
         ) 
         GROUP by KEY
         order by value desc
    </select>

### The error occurred while setting parameters
### SQL: resultType="KeyValuePair" parameterType="RequestFilter">         select KEY as key,sum(single_value) as value from (             select schcm.title as key             ,schuc.usage_count AS single_value              from usage_count schuc             inner join content_master schcm             on schcm.trace_id=schuc.trace_key             where schcm.pagetype='Page'                                             )           GROUP by KEY          order by value desc
### Cause: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

For the most part, regular SQL that runs in SQL Developer also runs when directly pasted into a MyBatis mapper file. Well, mostly anyway. I did remember to check for a semicolon at the end, but didn't make that mistake this time. What I'd like is for the MyBatis call to work like the SQL Developer call.

Update: Tried

   <select id="getTotalUniqueUsersByPage">
            resultType="KeyValuePair" 
           parameterType="RequestFilter">
       select 'SomeTitle' as "Key", 55 as "Value" from dual
    </select>

but that call fails as well with the same "ORA-00900: invalid SQL statement" In deference to comments below, I surrounded the alias names so that the reserved word "Key" should work, and indeed it works find in SQLDeveloper.

You need to alias your derived table (the select inside the parens). Basically ... from (select ...) <ALIAS> GROUP by KEY.... I can't imagine that SQL would work anywhere. — Andrew
– Andrew, Commented Sep 3, 2019 at 15:48
<select id="getTotalUniqueUsersByPage"> resultType="KeyValuePair" parameterType="RequestFilter"> select KEY as key,sum(single_value) as value from ( (select schcm.title as key ,schuc.usage_count AS single_value from usage_count schuc inner join content_master schcm on schcm.trace_id=schuc.trace_key where schcm.pagetype='Page') myselectalias ) GROUP by KEY order by value desc </select> — Woodsman
– Woodsman, Commented Sep 3, 2019 at 16:10
key is a reserved keyword. You need to quote it select "KEY" as "key" ... — user330315
– user330315, Commented Sep 3, 2019 at 16:18

Woodsman · Accepted Answer · 2019-09-03 18:16:00Z

1

So the problem was not the SQL. The problem was the extra closing > tag in the XML.

 <select id="getTotalUniqueUsersByPage">
            resultType="KeyValuePair" 
           parameterType="RequestFilter">
       select 'SomeTitle' as "Key", 55 as "Value" from dual
    </select>

should be

 <select id="getTotalUniqueUsersByPage"
            resultType="KeyValuePair" 
           parameterType="RequestFilter">
       select 'SomeTitle' as "Key", 55 as "Value" from dual
    </select>

To me, this is an XML validation error, but I'm not sure why it did not show up as such. I'm surprised it made it all the way to Oracle instead of complaining about bad XML instead.

answered Sep 3, 2019 at 18:16

Woodsman

1,1993 gold badges36 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query works in SQL Developer but has error in MyBatis

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related