Selecting alias column in Spring Boot Application using MyBatis Sql Query

Question

I'm working on Spring Boot Application using Maven and I'm using Sybase database and MyBatis as ORM framework.

I need to execute query like:

SELECT M.COL_A, M.COL_B, M.COL_C FROM MY_TABLE M 
UNION
SELECT N.COL_A, N.COL_B, "" AS COL_C FROM MY_TABLE2 N;

Now MY_TABLE2 does not have COL_C column but I need to select it for union clause to work.

This query works fine as my database but gives me following error when executed via MyBatis:

com.sybase.jdbc3.jdbc.SybSQLException: Invalid column name ''.

markp-fuso · Accepted Answer · 2017-06-12 16:10:32Z

1

Your application connection appears to be running with quoted_identifier enabled.

For example, switching out your table names for a couple system tables:

set quoted_identifier on
SELECT M.id, M.name, M.crdate FROM sysobjects M
UNION
SELECT N.id, N.name, "" AS COL_C FROM syscolumns N
go

Msg 207, Level 16, State 4:
Server 'ASE201', Line 2:
Invalid column name ''.

You can try disabling quoted_identifier in your application's connection (sorry, no idea how/where you'd do that in your application), or replace the double quotes with single quotes, eg:

set quoted_identifier on
SELECT M.id, M.name, M.crdate FROM sysobjects M
UNION
SELECT N.id, N.name, '' AS COL_C FROM syscolumns N
go

 id      name            crdate
 ------- --------------- -------------------
       1 sysobjects      May 31 2016 12:55PM
       2 sysindexes      May 31 2016 12:55PM
       3 syscolumns      May 31 2016 12:55PM
       4 systypes        May 31 2016 12:55PM
       8 syslogs         May 31 2016 12:55PM
... snip ...

answered Jun 12, 2017 at 16:10

markp-fuso

38.7k5 gold badges24 silver badges50 bronze badges

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

1 Comment

markp-fuso Over a year ago

@SuhasBadhe; great; if there are no other issues/concerns I recommend you mark the question as answered

Collectives™ on Stack Overflow

Selecting alias column in Spring Boot Application using MyBatis Sql Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related