EJB query which uses TRUNC and WITH

Question

I have a query which works fine when I use NativeQuery but I need to use an EJB query for my problem. Can anyone help me reformat my query so it would work for EJB also?

Here is the Native version

Query query = getEntityManager.createNativeQuery("WITH src as ("
+ "Select distinct TRUNC(CreationDate) a" 
+ "From myTable)"
+ "Select src.a from src Order by src.a desc");

The Class for myTable is an Entity type. The Properties are as followed

    @Id
    private Long Id; 

    @Column(nullable = false)
    private String Name;

    @Column(length = 30, nullable = false)
    private Timestamp CreationDate;

The properties are as above with setters and getters for them. (Except for ID) Thanks

What entity provider do you use? Hibernate? EclipseLink? OpenJPA? — neshkeev, Jul 31 '14 at 17:41

score 0 · Answer 1 · 2014-07-31T18:26:41.567

0

SELECT DISTINCT 
    TRUNC(CreationDate) as a
        FROM 
            (SELECT CreationDate  FROM myTable ORDER BY CreationDate DESC)

edited Jul 31 '14 at 18:26

answered Jul 31 '14 at 17:40

This query results in an exception saying 'could not resolve property a' – Salman9 Jul 31 '14 at 17:55
No still same error,it looks the same as before though! – Salman9 Jul 31 '14 at 18:04

neshkeev · Answer 2 · 2014-07-31T18:04:07.943

0

Try this:

Query query = getEntityManager.createQuery(
                "select DISTINCT TRUNC(src.CreationDate) 
                   from myTable src 
                  ORDER by src.CreationDate"
              );
List<Object[]> rows = query.list();
for (Object[] row: rows) {
    System.out.println("My date" + row[0]);
}

edited Jul 31 '14 at 18:04

answered Jul 31 '14 at 17:42

neshkeev

6,280
3
26
47

It does not work, order by would not work when you are using TRUNC in your select statement. It generates a GrammerException – Salman9 Jul 31 '14 at 18:03
What is wrong with `order by`? Get rid it during the debugging process. – neshkeev Jul 31 '14 at 18:05

EJB query which uses TRUNC and WITH

2 Answers2