Stack Exchange
How to fetch specific columns from PostgreSQL in spring boot
I'm using Postgres database. I want to fetch specific columns from a table.
The following query works fine when executing it in PgAdmin III, but throws the exception 

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 2, column 56

when executing through JAVA code.
Query:
SELECT 40 AS client_id,
       0 AS account_id,
       0 AS search_engine_id,
       'present' AS date_type,
       coalesce(stats.clicks, 0) AS clicks,
       d.day AS date
FROM (
  SELECT generate_series(timestamp '2018-06-10', timestamp '2018-06-16', interval '1 day')::date
) d (day)
  LEFT JOIN (
    SELECT 40 AS client_id,
           0 AS account_id,
           0 AS search_engine_id,
           date_type,
           sum(clicks) AS clicks,
           to_date(date, 'YYYY-MM-dd') AS date
    FROM ne_email_stats
    WHERE client_id = 40
      AND account_id IN (13, 14)
      AND date BETWEEN '2018-06-10'   AND '2018-06-16'
    GROUP BY date,
             date_type
  ) stats ON stats.date = d.day
ORDER BY date ASC

Code To Execute Query:
Query query = em.createQuery(pSqlQuery, EmailReportStats.class);
           emailReportStatses = query.getResultList();
           if (!emailReportStatses.isEmpty()) {
               for (EmailReportStats emailReport : emailReportStatses) {
                   emailReport.setDate(LocalDate.parse(emailReport.getDate()).format(DateTimeFormatter.ofPattern("dd MMM")));
                   emailReportStats.add(emailReport);
               }


That is a SQL query. EntityManager.createQuery() expects JPQL (or HQL depending on the JPA implementation you use). But SQL is not valid JPQL. And JPQL does not support advanced, database specific features. You will need to create a native query.

Just read some docs on JPQL syntax before ramming SQL into a query

Hi @a_horse_with_no_name, i've tried using create native query, Query query = em.createNativeQuery(pSqlQuery, EmailReportStats.class); It throws an exception stating that a few columns are missing.
