Está dando um erro ao executar o seguinte sql… alguem sabe me dizer o que pode ser isso?
Query q = em.createNativeQuery("select distinct(p.codigo_pessoa) "
+ ", p.nome_razao , ps.codigo_ibge , "
+ "p.cpf_cnpj , p.cpf_cnpj , p.insc_estadual , "
+ "c.codigo_ibge , p.inscricao_suframa , "
+ "en.logradouro , en.numero , en.complemento , "
+ "en.bairro from pessoa as p "
+ "inner join nfexml as nx on nx.pcliente = p.codigo_pessoa "
+ "left join endereco as en on en.codigo_endereco = p.endereco_padrao "
+ "left join cidade as c on c.codigo_cidade = en.cidade "
+ "left join pais as ps on ps.codigo_pais = c.codigo_pais "
+ "where nx.demi between :di and :df "
+ "union "
+ "select distinct(pe.codigo_pessoa), pe.nome_razao, "
+ "ps.codigo_ibge, pe.cpf_cnpj, "
+ "pe.cpf_cnpj, pe.insc_estadual, c.codigo_ibge , "
+ "pe.inscricao_suframa, "
+ "en.logradouro, en.numero, en.complemento, "
+ "en.bairro from pessoa as pe "
+ "inner join notafiscalentrada as nf on nf.fornecedor = pe.codigo_pessoa "
+ "left join endereco as en on en.codigo_endereco = pe.endereco_padrao "
+ "left join cidade as c on c.codigo_cidade = en.cidade "
+ "left join pais as ps on ps.codigo_pais = c.codigo_pais "
+ "where nf.dataemissao between :di and :df "
+ "order by pe.codigo_pessoa ");
q.setParameter("di", di);
q.setParameter("df", df);
o erro é o seguinte:
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:637)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:74)
at negocio.sped.SpedJpaController.pessoaByNFeXmlPeriodo(SpedJpaController.java:127)
at mb.sped.SpedFiscalCadMB.carrega150(SpedFiscalCadMB.java:168)
at mb.sped.SpedFiscalCadMB.setaValores(SpedFiscalCadMB.java:198)
at mb.sped.SpedFiscalCadMB.gerar(SpedFiscalCadMB.java:144)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at javax.el.BeanELResolver.invokeMethod(BeanELResolver.java:779)
at javax.el.BeanELResolver.invoke(BeanELResolver.java:528)
at javax.el.CompositeELResolver.invoke(CompositeELResolver.java:257)
at com.sun.el.parser.AstValue.invoke(AstValue.java:248)
at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:302)
at org.jboss.weld.util.el.ForwardingMethodExpression.invoke(ForwardingMethodExpression.java:39)
at org.jboss.weld.el.WeldMethodExpression.invoke(WeldMethodExpression.java:50)
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
... 37 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2231)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:65)
... 54 more
Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "pe"
Posição: 1015
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
... 61 more
Obrigado