title: JPQL Subquery - Exception this expression has an invalid table in this context
body: <p>I have a (inneficient, but working) T-SQL (sub)query</p>
  
  <pre><code>SELECT * FROM DIAGE.ade.UorPos WHERE Prefixo IN
    (SELECT PrefixoJurisdicionada FROM DIAGE.ade.Jurisdicionadas WHERE Prefixo =
      (SELECT Prefixo FROM DIAGE.ade.Jurisdicionadas WHERE PrefixoJurisdicionada = 8922)) 
  AND CodComissao IN (4345, 4346, 4347)
  </code></pre>
  
  <p>which I've build the correspondent JPQL</p>
  
  <pre><code>SELECT u FROM UorPos u WHERE u.prefixo IN 
    (SELECT j.prefixoJurisdicionada FROM Jurisdicionadas j WHERE j.prefixo = 
      (SELECT j.prefixo FROM Jurisdicionadas j WHERE j.prefixoJurisdicionada = :prefixo)) 
  AND u.codComissao IN (4345, 4346, 4347)
  </code></pre>
  
  <p>Despite compiling, when running launches the Exception:</p>
  
  <pre><code>[...]
  Caused by: Exception [EclipseLink-6069] (Eclipse Persistence Services - 
  2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.QueryException
  Exception Description: The field [DIAGE.ade.Prefixos.Prefixo] in this 
  expression has an invalid table in this context.
  Query: ReadAllQuery(name="UorPos.findUorPosExecutivosByPrefixo" 
  referenceClass=UorPos jpql="SELECT u FROM UorPos u WHERE u.prefixo IN 
  (SELECT j.prefixoJurisdicionada FROM Jurisdicionadas j WHERE j.prefixo = 
  (SELECT j.prefixo FROM Jurisdicionadas j WHERE j.prefixoJurisdicionada = 
  :prefixo)) AND u.codComissao IN (4345, 4346, 4347)") at 
  org.eclipse.persistence.exceptions.QueryException. invalidTableForFieldInExpression (QueryException.java:749)
  at org.eclipse.persistence.internal.expressions.FieldExpression.validateNode(FieldExpression.java:296)
  at org.eclipse.persistence.expressions.Expression.normalize(Expression.java:3275)
  at org.eclipse.persistence.internal.expressions.DataExpression.normalize(DataExpression.java:369)
  at org.eclipse.persistence.internal.expressions.FieldExpression.normalize(FieldExpression.java:223)
  </code></pre>
  
  <p>I've made some research and, in my understanding, my JPQL query is ok.</p>
  
  <p>Can anyone help me to resolve this?</p>
  
  <p>Some links I've researched:</p>
  
  <ul>
  <li><p><a href="https://openjpa.apache.org/builds/1.2.3/apache-openjpa/docs/jpa_langref.html#jpa_langref_subqueries" rel="nofollow noreferrer">item 2.5.15</a></p></li>
  <li><p><a href="https://stackoverflow.com/questions/3629259/how-do-i-do-a-jpql-subquery?utm_medium=organic&amp;utm_source=google_rich_qa&amp;utm_campaign=google_rich_qa">How do I do a JPQL SubQuery?</a></p></li>
  <li><p><a href="https://www.thoughts-on-java.org/query-complex-jpa-hibernate/" rel="nofollow noreferrer">item 5</a></p></li>
  <li><p><a href="https://docs.oracle.com/html/E13946_04/ejb3_langref.html#ejb3_langref_subqueries" rel="nofollow noreferrer">item 10.2.5.15</a></p></li>
  </ul>
  
  <p><a href="http://what-when-how.com/enterprise-javabeans-3/introducing-jpql-part-3-ejb-3/" rel="nofollow noreferrer">Using IN with a subquery</a></p>
  
  <p>I'm using EclipseLink version 2.5.2 (integrated with Netbeans 8.0.2), Glassfish 4.1, Java 1.7.0_71.</p>
  
  <p>UPDATED</p>
  
  <p>The UorPos entity:</p>
  
  <pre><code>@Entity
  @Table(name = "UorPos", catalog = "DIAGE", schema = "ade")
  @XmlRootElement
  @NamedQueries({
  @NamedQuery(name="UorPos.findXByY", query="SELECT u FROM UorPos u WHERE u.prefixo IN
  (SELECT j.prefixoJurisdicionada FROM Jurisdicionadas j WHERE j.prefixo =
  (SELECT j.prefixo FROM Jurisdicionadas j WHERE j.prefixoJurisdicionada = :prefixo))
  AND u.codComissao IN (4345, 4346, 4347)")
  })
  
  public class UorPos implements Serializable {
  
      private static final long serialVersionUID = 1L;
      @Id
      @Basic(optional = false)
      @NotNull
      @Size(min = 1, max = 8)
      @Column(name = "Matricula")
      private String matricula;
      @Size(max = 200)
      @Column(name = "Nome")
      private String nome;
      @Size(max = 200)
      @Column(name = "NomeGuerra")
      private String nomeGuerra;
      @Column(name = "CodComissao")
      private Integer codComissao;
      @Size(max = 25)
      @Column(name = "NomeComissao")
      private String nomeComissao;
      @Size(max = 4)
      @Column(name = "CodNivel")
      private String codNivel;
      @Size(max = 50)
      @Column(name = "DescNivel")
      private String descNivel;
      @Size(max = 50)
      @Column(name = "eMailFuncionario")
      private String eMailFuncionario;
      @Column(name = "DataCaptura")
      @Temporal(TemporalType.TIMESTAMP)
      private Date dataCaptura;
      @Column(name = "DataPermissaoAcesso")
      @Temporal(TemporalType.TIMESTAMP)
      private Date dataPermissaoAcesso;
      @ManyToMany(mappedBy = "uorPosCollection")
      private Collection&lt;Demandas&gt; demandasCollection;
      @ManyToMany(mappedBy = "uorPosCollection1")
      private Collection&lt;Demandas&gt; demandasCollection1;
      @ManyToMany(mappedBy = "uorPosCollection2")
      private Collection&lt;Demandas&gt; demandasCollection2;
      @JoinColumn(name = "UORpos", referencedColumnName = "UORpos")
      @ManyToOne(optional = false)
      private Divisoes uORpos;
      @JoinColumn(name = "idPermissaoAcesso", referencedColumnName = "idPermissaoAcesso")
      @ManyToOne
      private PermissoesAcesso idPermissaoAcesso;
      @JoinColumn(name = "Prefixo", referencedColumnName = "Prefixo")
      @ManyToOne
      private Prefixos prefixo;
      @OneToMany(mappedBy = "matricula")
      private Collection&lt;Anotacoes&gt; anotacoesCollection;
      @OneToMany(mappedBy = "matricula")
      private Collection&lt;Log&gt; logCollection;
  
      public UorPos() {
      }
  
      public UorPos(String matricula) {
          this.matricula = matricula;
      }
  
      public String getMatricula() {
          return matricula;
      }
  
      public void setMatricula(String matricula) {
          this.matricula = matricula;
      }
  
      public String getNome() {
          return nome;
      }
  
      public void setNome(String nome) {
          this.nome = nome;
      }
  
      public String getNomeGuerra() {
          return nomeGuerra;
      }
  
      public void setNomeGuerra(String nomeGuerra) {
          this.nomeGuerra = nomeGuerra;
      }
  
      public Integer getCodComissao() {
          return codComissao;
      }
  
      public void setCodComissao(Integer codComissao) {
          this.codComissao = codComissao;
      }
  
      public String getNomeComissao() {
          return nomeComissao;
      }
  
      public void setNomeComissao(String nomeComissao) {
          this.nomeComissao = nomeComissao;
      }
  
      public String getCodNivel() {
          return codNivel;
      }
  
      public void setCodNivel(String codNivel) {
          this.codNivel = codNivel;
      }
  
      public String getDescNivel() {
          return descNivel;
      }
  
      public void setDescNivel(String descNivel) {
          this.descNivel = descNivel;
      }
  
      public String getEMailFuncionario() {
          return eMailFuncionario;
      }
  
      public void setEMailFuncionario(String eMailFuncionario) {
          this.eMailFuncionario = eMailFuncionario;
      }
  
      public Date getDataCaptura() {
          return dataCaptura;
      }
  
      public void setDataCaptura(Date dataCaptura) {
          this.dataCaptura = dataCaptura;
      }
  
      public Date getDataPermissaoAcesso() {
          return dataPermissaoAcesso;
      }
  
      public void setDataPermissaoAcesso(Date dataPermissaoAcesso) {
          this.dataPermissaoAcesso = dataPermissaoAcesso;
      }
  
      @XmlTransient
      public Collection&lt;Demandas&gt; getDemandasCollection() {
          return demandasCollection;
      }
  
      public void setDemandasCollection(Collection&lt;Demandas&gt; demandasCollection) {
          this.demandasCollection = demandasCollection;
      }
  
      @XmlTransient
      public Collection&lt;Demandas&gt; getDemandasCollection1() {
          return demandasCollection1;
      }
  
      public void setDemandasCollection1(Collection&lt;Demandas&gt; demandasCollection1) {
          this.demandasCollection1 = demandasCollection1;
      }
  
      @XmlTransient
      public Collection&lt;Demandas&gt; getDemandasCollection2() {
          return demandasCollection2;
      }
  
      public void setDemandasCollection2(Collection&lt;Demandas&gt; demandasCollection2) {
          this.demandasCollection2 = demandasCollection2;
      }
  
      public Divisoes getUORpos() {
          return uORpos;
      }
  
      public void setUORpos(Divisoes uORpos) {
          this.uORpos = uORpos;
      }
  
      public PermissoesAcesso getIdPermissaoAcesso() {
          return idPermissaoAcesso;
      }
  
      public void setIdPermissaoAcesso(PermissoesAcesso idPermissaoAcesso) {
          this.idPermissaoAcesso = idPermissaoAcesso;
      }
  
      public Prefixos getPrefixo() {
          return prefixo;
      }
  
      public void setPrefixo(Prefixos prefixo) {
          this.prefixo = prefixo;
      }
  
      @XmlTransient
      public Collection&lt;Anotacoes&gt; getAnotacoesCollection() {
          return anotacoesCollection;
      }
  
      public void setAnotacoesCollection(Collection&lt;Anotacoes&gt; anotacoesCollection) {
          this.anotacoesCollection = anotacoesCollection;
      }
  
      @XmlTransient
      public Collection&lt;Log&gt; getLogCollection() {
          return logCollection;
      }
  
      public void setLogCollection(Collection&lt;Log&gt; logCollection) {
          this.logCollection = logCollection;
      }
  
      @Override
      public int hashCode() {
          int hash = 0;
          hash += (matricula != null ? matricula.hashCode() : 0);
          return hash;
      }
  
      @Override
      public boolean equals(Object object) {
          if (!(object instanceof UorPos)) {
              return false;
          }
          UorPos other = (UorPos) object;
          if ((this.matricula == null &amp;&amp; other.matricula != null) || (this.matricula != null &amp;&amp; !this.matricula.equals(other.matricula))) {
              return false;
          }
          return true;
      }
  
      @Override
      public String toString() {
          return "br.com.bb.uop.dcvipat.ade.entity.UorPos[ matricula=" + matricula + " ]";
      }
  
  }
  </code></pre>
  
  <p>The Jurisdicionadas entity:</p>
  
  <pre><code>@Entity
  @Table(name = "Jurisdicionadas", catalog = "DIAGE", schema = "ade")
  @XmlRootElement
  @NamedQueries({
  @NamedQuery(name="Jurisdicionadas.findAll", query="SELECT j FROM Jurisdicionadas j")})
  public class Jurisdicionadas implements Serializable {
  
      private static final long serialVersionUID = 1L;
      @Id
      @Basic(optional = false)
      @GeneratedValue (strategy = GenerationType.IDENTITY)
      @Column(name = "id")
      private Integer id;
      @Column(name = "PrefixoJurisdicionada")
      private Integer prefixoJurisdicionada;
      @Size(max = 200)
      @Column(name = "NomePrefixoJurisdicionada")
      private String nomePrefixoJurisdicionada;
      @JoinColumn(name = "Prefixo", referencedColumnName = "Prefixo")
      @ManyToOne
      private Prefixos prefixo;
  
      public Jurisdicionadas() {
      }
  
      public Jurisdicionadas(Integer id) {
          this.id = id;
      }
  
      public Integer getId() {
          return id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      public Integer getPrefixoJurisdicionada() {
          return prefixoJurisdicionada;
      }
  
      public void setPrefixoJurisdicionada(Integer prefixoJurisdicionada) {
          this.prefixoJurisdicionada = prefixoJurisdicionada;
      }
  
      public String getNomePrefixoJurisdicionada() {
          return nomePrefixoJurisdicionada;
      }
  
      public void setNomePrefixoJurisdicionada(String nomePrefixoJurisdicionada) {
          this.nomePrefixoJurisdicionada = nomePrefixoJurisdicionada;
      }
  
      public Prefixos getPrefixo() {
          return prefixo;
      }
  
      public void setPrefixo(Prefixos prefixo) {
          this.prefixo = prefixo;
      }
  
      @Override
      public int hashCode() {
          int hash = 0;
          hash += (id != null ? id.hashCode() : 0);
          return hash;
      }
  
      @Override
      public boolean equals(Object object) {
          if (!(object instanceof Jurisdicionadas)) {
              return false;
          }
          Jurisdicionadas other = (Jurisdicionadas) object;
          if ((this.id == null &amp;&amp; other.id != null) || (this.id != null &amp;&amp; !this.id.equals(other.id))) {
              return false;
          }
          return true;
      }
  
      @Override
      public String toString() {
          return "br.com.bb.uop.dcvipat.ade.entity.Jurisdicionadas[ id=" + id + " ]";
      }
  }
  </code></pre>
  
  <p>Thanks in advance.</p>