zwwcn

Just another WordPress.com site

Concat in JPA native query

I am writing some native query to record customized info.

SELECT COUNT(DISTINCT i.OBJECT_ID),CONCAT(YEAR(i.DATE) ,'-',MONTH(i.DATE)) as yearDate FROM OP_INCIDENT i GROUP BY YEAR(i.DATE),MONTH(i.DATE)

The above query is supposed to generate data in format like [2,’2015-09′], but what I got is [2,[54,43,41,49,50]]. Instead giving a string year-month value, it gives me a data array.

An explicit type cast sovle the problem:

SELECT COUNT(DISTINCT i.OBJECT_ID),CONCAT(CAST(YEAR(i.DATE) AS CHAR),'-',CAST(MONTH(i.DATE) AS CHAR)) as yearDate FROM OP_INCIDENT i GROUP BY YEAR(i.DATE),MONTH(i.DATE)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: