Query problem with SQL function : dayofweek

Support
#1

Hello,
The following SQL function works fine when there is no dayofweek clause.
I need to be able to do calculations on certain days of the week and when I add the dayofweek clause, my query no longer works.

try (Transaction tx1 = persistence.createTransaction()) {

        TypedQuery<ACD> queryATraiter = persistence.getEntityManager().createQuery("select c.debutplage, " +
                                                                                            "avg(c.appelsRecusOuverts) " +
                                                                                            "from avancial$ACD c " +
                                                                                            "where ((c.date between :dDebut and :dFin) AND (c.debutplage between :hdebut and :hfin) " +
                                                                                            "AND (c.debutplage < :hfin)) " +
                                                                                            "AND (dayofweek(c.date) = :dDay) " +
                                                                                            "group by c.debutplage, c.finplage", ACD.class);
        queryATraiter.setParameter("dDebut", dateDebut);
        queryATraiter.setParameter("dFin", dateFin);
        queryATraiter.setParameter("hdebut", heureDebutPlage);
        queryATraiter.setParameter("hfin", heureFinPlage);
      queryATraiter.setParameter("dDay", 1);

        List<ACD> aCD = queryATraiter.getResultList();
        tx1.commit();

Error message :

JpqlSyntaxException: Errors found for input jpql:[select c.debutplage, avg(c.appelsRecusOuverts) from avancial$ACD c where ((c.date between :dDebut and :dFin) AND (c.debutplage between :hdebut and :hfin) AND (c.debutplage < :hfin)) AND (dayofweek(c.date) = :dDay) group by c.debutplage, c.finplage]
CommonErrorNode [<unexpected: [@70,196:196=’(’,<23>,1:196], resync=dayofweek(c.date>]

Thank you for your help,
regards,
Stephane

1 Like
#2

Hi,

You are executing JPQL, so you can use only standard JPQL functions and some extensions provided by EclipseLink and supported by CUBA. None of them contain DayOfWeek, so you should either use Function in your JPQL or switch completely to SQL and create the query using the persistence.getEntityManager().createNativeQuery() method.

1 Like
#3

Hello Konstantin,

thank you for your reply.
Now I use persistence.getEntityManager (). CreateNativeQuery () method but I encounter a new problem:

SQLSyntaxErrorException: user lacks privilege or object not found: xxx

My query is executed from a bean,

Thank you for you precious help
Stephane

#4

Such error can occur only if your SQL is invalid, like you are trying to select from a non-existent table.

1 Like
#5

Hi,
Thanks !!!
Actually it was a field name problem (and table name). I used the “name” attribute and not the “column” attribute of the field.