Problème de SELECT dans un FROM

Bonjour j'ai besoin de faire un select dans un from.

J'ai utiliser le code suivant :

Code:

1
2
 
    @NamedQuery(name = "SessionPersonne.findAllPremiereVisiteByPays",query = "SELECT t1.cDate , t1.idT1 FROM ( SELECT cast( p.momentDePassage as date)  as cDate , COUNT(p)as countById ,p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p GROUP BY cDate,idT1  ) AS t1  "),

et j'ai le message d'erreur suivant :

Code:

1
2
3
4
5
6
7
8
9
10
 
ejb: 2016-03-15 14:39:00.472--ServerSession(1092521071)--Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Problem compiling [SELECT t1.cDate , t1.idT1 FROM ( SELECT cast( p.momentDePassage as date)  as cDate , COUNT(p)as countById ,p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p GROUP BY cDate,idT1  ) AS t1  ]. 
[30, 190] '(SELECT cast(p.momentDePassage as date) as cDate, COUNT(p) as countById, p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p GROUP BY cDate, idT1) AS t1' cannot be the first declaration of the FROM clause.
ServletContextListener destroyed
15-Mar-2016 14:39:00.472 SEVERE [http-nio-8084-exec-220] org.apache.catalina.core.StandardContext.startInternal One or more listeners failed to start. Full details will be found in the appropriate container log file
15-Mar-2016 14:39:00.480 SEVERE [http-nio-8084-exec-220] org.apache.catalina.core.StandardContext.startInternal Erreur de démarrage du contexte [/Client] suite aux erreurs précédentes
15-Mar-2016 14:39:00.484 WARNING [http-nio-8084-exec-220] org.apache.catalina.loader.WebappClassLoaderBase.clearReferencesJdbc The web application [Client] registered the JDBC driver [com.microsoft.sqlserver.jdbc.SQLServerDriver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
15-Mar-2016 14:39:00.485 SEVERE [http-nio-8084-exec-220] org.apache.catalina.loader.WebappClassLoaderBase.checkThreadLocalMapForLeaks The web application [Client] created a ThreadLocal with key of type [com.microsoft.sqlserver.jdbc.ActivityCorrelator$1] (value [com.microsoft.sqlserver.jdbc.ActivityCorrelator$1@6bc3645b]) and a value of type [com.microsoft.sqlserver.jdbc.ActivityId] (value [4aae6d29-6c81-4d16-a84c-02bff9e498e5-2]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.
15-Mar-2016 14:39:00.486 INFO [http-nio-8084-exec-220] org.apache.catalina.startup.HostConfig.deployDescriptor Deployment of configuration descriptor

Pouvez vous m'aidez ?

Merci d'avance.

Je ne sais pas si cela y fait quelque chose,
mais j'écrirais plutôt le GROUP BY comme cela :

Code:

1
2
3
4
5
6
7
SELECT t1.cDate , t1.idT1 
FROM ( 
            SELECT cast( p.momentDePassage as date)  as cDate , COUNT(p) as countById ,p.sessionPersonne.keySessionPersonne as idT1 
            FROM Passage p 
            GROUP BY cast( p.momentDePassage as date), p.sessionPersonne.keySessionPersonne
      ) AS t1

toujours un probléme

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
 
15-Mar-2016 17:38:14.434 SEVERE [http-nio-8084-exec-8] org.apache.catalina.core.StandardContext.listenerStart Exception lors de l'envoi de l'évènement contexte initialisé (context initialized) à l'instance de classe d'écoute (listener) fr.ietevents.client.Securite.MyAppServletContextListener
 javax.persistence.PersistenceException: Exception [EclipseLink-28019] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.EntityManagerSetupException
Exception Description: Deployment of PersistenceUnit [fr.ietevents_Client_war_1.0-SNAPSHOTPU] failed. Close all factories for this PersistenceUnit.
Internal Exception: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Problem compiling [SELECT t1.cDate , t1.idT1 FROM (SELECT cast( p.momentDePassage as date)  as cDate , COUNT(p) as countById ,p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p  GROUP BY cast( p.momentDePassage as date), p.sessionPersonne.keySessionPersonne  ) AS t1  ]. 
[30, 248] '(SELECT cast(p.momentDePassage as date) as cDate, COUNT(p) as countById, p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p GROUP BY cast(p.momentDePassage as date), p.sessionPersonne.keySessionPersonne) AS t1' cannot be the first declaration of the FROM clause.
	at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.createDeployFailedPersistenceException(EntityManagerSetupImpl.java:820)
	at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:760)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.getAbstractSession(EntityManagerFactoryDelegate.java:204)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.getDatabaseSession(EntityManagerFactoryDelegate.java:182)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.getDatabaseSession(EntityManagerFactoryImpl.java:527)
	at org.eclipse.persistence.jpa.PersistenceProvider.createEntityManagerFactoryImpl(PersistenceProvider.java:140)
	at org.eclipse.persistence.jpa.PersistenceProvider.createEntityManagerFactory(PersistenceProvider.java:177)
	at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:79)
	at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:54)
	at fr.ietevents.client.Facades.PaysFacade.getEntityManager(PaysFacade.java:23)
	at fr.ietevents.client.Facades.AbstractFacade.findAll(AbstractFacade.java:110)
	at fr.ietevents.client.Securite.MyAppServletContextListener.contextInitialized(MyAppServletContextListener.java:43)
	at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4738)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5181)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:725)
	at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:701)
	at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
	at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:586)
	at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:460)
	at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1586)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.apache.tomcat.util.modeler.BaseModelMBean.invoke(BaseModelMBean.java:300)
	at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:819)
	at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:801)
	at org.apache.catalina.manager.ManagerServlet.check(ManagerServlet.java:1460)
	at org.apache.catalina.manager.ManagerServlet.deploy(ManagerServlet.java:906)
	at org.apache.catalina.manager.ManagerServlet.doGet(ManagerServlet.java:344)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:217)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:614)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:673)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Caused by: Exception [EclipseLink-28019] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.EntityManagerSetupException
Exception Description: Deployment of PersistenceUnit [fr.ietevents_Client_war_1.0-SNAPSHOTPU] failed. Close all factories for this PersistenceUnit.
Internal Exception: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Problem compiling [SELECT t1.cDate , t1.idT1 FROM (SELECT cast( p.momentDePassage as date)  as cDate , COUNT(p) as countById ,p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p  GROUP BY cast( p.momentDePassage as date), p.sessionPersonne.keySessionPersonne  ) AS t1  ]. 
[30, 248] '(SELECT cast(p.momentDePassage as date) as cDate, COUNT(p) as countById, p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p GROUP BY cast(p.momentDePassage as date), p.sessionPersonne.keySessionPersonne) AS t1' cannot be the first declaration of the FROM clause.
	at org.eclipse.persistence.exceptions.EntityManagerSetupException.deployFailed(EntityManagerSetupException.java:238)
	... 60 more
Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Problem compiling [SELECT t1.cDate , t1.idT1 FROM (SELECT cast( p.momentDePassage as date)  as cDate , COUNT(p) as countById ,p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p  GROUP BY cast( p.momentDePassage as date), p.sessionPersonne.keySessionPersonne  ) AS t1  ]. 
[30, 248] '(SELECT cast(p.momentDePassage as date) as cDate, COUNT(p) as countById, p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p GROUP BY cast(p.momentDePassage as date), p.sessionPersonne.keySessionPersonne) AS t1' cannot be the first declaration of the FROM clause.
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:155)
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.validate(HermesParser.java:347)
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:278)
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:163)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:142)
	at org.eclipse.persistence.internal.jpa.JPAQuery.processJPQLQuery(JPAQuery.java:221)
	at org.eclipse.persistence.internal.jpa.JPAQuery.prepare(JPAQuery.java:182)
	at org.eclipse.persistence.queries.DatabaseQuery.prepareInternal(DatabaseQuery.java:621)
	at org.eclipse.persistence.internal.sessions.AbstractSession.processJPAQuery(AbstractSession.java:4309)
	at org.eclipse.persistence.internal.sessions.AbstractSession.processJPAQueries(AbstractSession.java:4270)
	at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.initializeDescriptors(DatabaseSessionImpl.java:579)
	at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.postConnectDatasource(DatabaseSessionImpl.java:799)
	at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.loginAndDetectDatasource(DatabaseSessionImpl.java:743)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryProvider.login(EntityManagerFactoryProvider.java:239)
	at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:685)
	... 58 more
 
15-Mar-2016 17:38:27.002 SEVERE [http-nio-8084-exec-10] org.apache.catalina.core.StandardContext.listenerStart Exception lors de l'envoi de l'évènement contexte initialisé (context initialized) à l'instance de classe d'écoute (listener) fr.ietevents.client.Securite.MyAppServletContextListener
 javax.persistence.PersistenceException: Exception [EclipseLink-28019] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.EntityManagerSetupException
Exception Description: Deployment of PersistenceUnit [fr.ietevents_Client_war_1.0-SNAPSHOTPU] failed. Close all factories for this PersistenceUnit.
Internal Exception: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Problem compiling [SELECT t1.cDate , t1.idT1 FROM (SELECT cast( p.momentDePassage as date)  as cDate , COUNT(p) as countById ,p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p  GROUP BY cast( p.momentDePassage as date), p.sessionPersonne.keySessionPersonne  ) AS t1  ]. 
[30, 248] '(SELECT cast(p.momentDePassage as date) as cDate, COUNT(p) as countById, p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p GROUP BY cast(p.momentDePassage as date), p.sessionPersonne.keySessionPersonne) AS t1' cannot be the first declaration of the FROM clause.
	at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.createDeployFailedPersistenceException(EntityManagerSetupImpl.java:820)
	at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:760)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.getAbstractSession(EntityManagerFactoryDelegate.java:204)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.getDatabaseSession(EntityManagerFactoryDelegate.java:182)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.getDatabaseSession(EntityManagerFactoryImpl.java:527)
	at org.eclipse.persistence.jpa.PersistenceProvider.createEntityManagerFactoryImpl(PersistenceProvider.java:140)
	at org.eclipse.persistence.jpa.PersistenceProvider.createEntityManagerFactory(PersistenceProvider.java:177)
	at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:79)
	at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:54)
	at fr.ietevents.client.Facades.PaysFacade.getEntityManager(PaysFacade.java:23)
	at fr.ietevents.client.Facades.AbstractFacade.findAll(AbstractFacade.java:110)
	at fr.ietevents.client.Securite.MyAppServletContextListener.contextInitialized(MyAppServletContextListener.java:43)
	at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4738)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5181)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:725)
	at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:701)
	at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
	at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:586)
	at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:460)
	at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1586)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.apache.tomcat.util.modeler.BaseModelMBean.invoke(BaseModelMBean.java:300)
	at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:819)
	at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:801)
	at org.apache.catalina.manager.ManagerServlet.check(ManagerServlet.java:1460)
	at org.apache.catalina.manager.ManagerServlet.deploy(ManagerServlet.java:906)
	at org.apache.catalina.manager.ManagerServlet.doGet(ManagerServlet.java:344)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:217)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:614)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:673)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Caused by: Exception [EclipseLink-28019] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.EntityManagerSetupException
Exception Description: Deployment of PersistenceUnit [fr.ietevents_Client_war_1.0-SNAPSHOTPU] failed. Close all factories for this PersistenceUnit.
Internal Exception: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Problem compiling [SELECT t1.cDate , t1.idT1 FROM (SELECT cast( p.momentDePassage as date)  as cDate , COUNT(p) as countById ,p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p  GROUP BY cast( p.momentDePassage as date), p.sessionPersonne.keySessionPersonne  ) AS t1  ]. 
[30, 248] '(SELECT cast(p.momentDePassage as date) as cDate, COUNT(p) as countById, p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p GROUP BY cast(p.momentDePassage as date), p.sessionPersonne.keySessionPersonne) AS t1' cannot be the first declaration of the FROM clause.
	at org.eclipse.persistence.exceptions.EntityManagerSetupException.deployFailed(EntityManagerSetupException.java:238)
	... 60 more
Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Problem compiling [SELECT t1.cDate , t1.idT1 FROM (SELECT cast( p.momentDePassage as date)  as cDate , COUNT(p) as countById ,p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p  GROUP BY cast( p.momentDePassage as date), p.sessionPersonne.keySessionPersonne  ) AS t1  ]. 
[30, 248] '(SELECT cast(p.momentDePassage as date) as cDate, COUNT(p) as countById, p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p GROUP BY cast(p.momentDePassage as date), p.sessionPersonne.keySessionPersonne) AS t1' cannot be the first declaration of the FROM clause.
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:155)
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.validate(HermesParser.java:347)
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:278)
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:163)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:142)
	at org.eclipse.persistence.internal.jpa.JPAQuery.processJPQLQuery(JPAQuery.java:221)
	at org.eclipse.persistence.internal.jpa.JPAQuery.prepare(JPAQuery.java:182)
	at org.eclipse.persistence.queries.DatabaseQuery.prepareInternal(DatabaseQuery.java:621)
	at org.eclipse.persistence.internal.sessions.AbstractSession.processJPAQuery(AbstractSession.java:4309)
	at org.eclipse.persistence.internal.sessions.AbstractSession.processJPAQueries(AbstractSession.java:4270)
	at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.initializeDescriptors(DatabaseSessionImpl.java:579)
	at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.postConnectDatasource(DatabaseSessionImpl.java:799)
	at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.loginAndDetectDatasource(DatabaseSessionImpl.java:743)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryProvider.login(EntityManagerFactoryProvider.java:239)
	at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:685)
	... 58 more

Tu dois faire une native query, JPQL n'autorise pas les subselect dans les clauses from. JPA est orienté objet, et il faut des objets dans la clause from. Et je ne vois pas l'intérêt de ton subselect, cette requête est plus simple et la même il me semble. Tu ne fais un subselect que pour en reprendre les éléments.

Code:

SELECT p.momentDePassage as cDate , p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p GROUP BY cDate,idT1

je voudrais faire cette requête pour faire ensuite un INNER Join :

Code:

@NamedQuery(name = "SessionPersonne.findAllPremiereVisiteByPays",query = "SELECT count(t1.countById),s.keySessionPersonne FROM (SELECT cast( p.momentDePassage as date) as cDate , COUNT(p) as countById ,p.sessionPersonne.keySessionPersonne as idT1 FROM Passage p GROUP BY cast( p.momentDePassage as date), p.sessionPersonne.keySessionPersonne ) AS t1 INNER JOIN SessionPersonne s ON (idT1.id =s.passageCollection) GROUP BY t1.id "),

pour compter le nombre de date différente que compter passage je peux faire ca comment si je peux pas utliser les subselect ?

j'avais fait le code suivant :

Code:

@NamedQuery(name = "SessionPersonne.findAllPasVisiteByCategories",query = "SELECT COUNT(p),cat.nomCategorie FROM SessionPersonne s JOIN s.personne p, s.categorie cat , s.session session WHERE session.keySession = :keySession and size(s.passageCollection) = 1 and s.dateCreation BETWEEN :statDate and :endDate GROUP BY cat.nomCategorie "),

le souci si dans passageCollection a 2 fois la même date il va la compter quand même alors que je veux la compter que pour 1 passage

Si je me rappelle bien SELECT DISTINCT permet d'éviter les doublons, mais je me trompe peut-être.

je fais comment sur where size(s.passageCollection) = 1 pour cast( p.momentDePassage as date) et ensuite faire un distinct ? dans un where

personne saurais m'expliquer comment faire pour sélectionner que certain entrée de la liste d'entrée par client ?

Tu souhaites avoir :
- les personnes ayant une seule visite par pays
ou bien
- les personnes ayant une seule visite par pays à une date précise

les personnes ayant une seule visite par pays . le souci c'est que si y 2 entrer a la même date ça compte pour 1 seul entrée .

Encore une question :
si une personne fait, par exemple, deux visites en Belgique :
- une le 5 mars 2016
- l'autre le 8 mars 2016

-> tu veux compter une ou deux entrées ?
-> si tu n'en compte qu'une est-ce que tu veux seulement la première ?

si une personne fait, par exemple, deux visites en Belgique :
- une le 5 mars 2016
- l'autre le 8 mars 2016

ça compte pour 2

si une personne fait,par exemple , deux visites en belgique :

-une le 5mars 2016
et une autre le 5mars 2016
çà compte pour 1

La réponse en SQL est :
Code:

1 2 3 SELECT idT1 FROM (SELECT DISTINCT cast( p.momentDePassage as date) as cDate, p.sessionPersonne.keySessionPersonne as idT1 ) As T GROUP BY idT1 HAVING COUNT(*) = 1
Je pense, comme l'a dit Tchize_, qu'on ne va pas échapper au SQL Natif

bonjour,

pourquoi j'ai le message d'erreur suivant :

Code:

1
2
3
4
5
6
7
Caused by: Exception [EclipseLink-28019] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.EntityManagerSetupException
Exception Description: Deployment of PersistenceUnit [fr.ietevents_Client_war_1.0-SNAPSHOTPU] failed. Close all factories for this PersistenceUnit.
Internal Exception: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing [SELECT idT1 FROM (SELECT DISTINCT cast( p.momentDePassage as date)  as cDate, p.sessionPersonne.keySessionPersonne as idT1 ) As T GROUP BY idT1 HAVING COUNT(*) = 1]. 
[121, 121] A select statement must have a FROM clause.
[155, 155] The left expression is missing from the arithmetic expression.
[156, 156] The right expression is missing from the arithmetic expression.

Ah quel âne !
j'ai oublié de mettre la clause FROM !!!

il faut faire :
Code:

1 2 3 SELECT idT1 FROM (SELECT DISTINCT cast( p.momentDePassage as date) as cDate, p.sessionPersonne.keySessionPersonne as idT1 FROM Passage) As T GROUP BY idT1 HAVING COUNT(*) = 1
Cela dit, tu avais le droit de regarder le message d'erreur toi aussi ... ;)

Citation:

A select statement must have a FROM clause