Ryan - did the work around solve the issue? Thanks, James
On Fri, Sep 22, 2017 at 11:22 AM, Maryann Xue <maryann....@gmail.com> wrote: > Hi Ryan, > > Could you please try: > > select > > MAIN.EBELN as PO_DOC, > > MAIN.EBELP as PO_DOC_ITEM_NUM, > > XREF.DELV_DOC > > from EKPO as MAIN > > left outer join > > (select distinct EBELN as PO_DOC, EBELP as PO_DOC_ITEM_NUM, BELNR as > DELV_DOC from EKBE > > where BEWTP = 'L' > > and MENGE != 0 > > and VGABE = '8') as XREF > > on MAIN.EBELN = XREF.PO_DOC and main.EBELP = XREF.PO_DOC_ITEM_NUM; > > > instead? > > > FYI, columns not projected in the "SELECT" in a subquery are not visible > to outer scopes. > > > Let me know if it works. > > > > Thanks, > > Maryann > > On Fri, Sep 22, 2017 at 7:59 AM, Ryan Templeton < > rtemple...@hortonworks.com> wrote: > >> Sorry, forgot to attach the ddl for the tables being used… >> >> >> >> >> >> >> >> *From: *Ryan Templeton <rtemple...@hortonworks.com> >> *Reply-To: *"user@phoenix.apache.org" <user@phoenix.apache.org> >> *Date: *Friday, September 22, 2017 at 9:54 AM >> *To: *"user@phoenix.apache.org" <user@phoenix.apache.org> >> *Subject: *Phoenix join on derived table and documentation >> >> >> >> Hey guys, >> >> >> >> I’m attempting to execute a join against a derived table as documented >> here: >> >> http://phoenix.apache.org/joins.html#grouped-joins-and-derived-tables >> >> >> >> >> >> My query is nearly identical in composition to the documentation but I’m >> still getting compilation errors from Phoenix. Can anyone help? Using >> Phoenix 4.7 - (HDP 2.6) >> >> >> >> select >> >> MAIN.EBELN as PO_DOC, >> >> MAIN.EBELP as PO_DOC_ITEM_NUM, >> >> XREF.DELV_DOC >> >> from EKPO as MAIN >> >> left outer join >> >> (select distinct EBELN as PO_DOC, EBELP as PO_DOC_ITEM_NUM, BELNR as >> DELV_DOC from EKBE >> >> where BEWTP = 'L' >> >> and MENGE != 0 >> >> and VGABE = '8') as XREF >> >> on MAIN.EBELN = XREF.EBELN and main.EBELP = XREF.EBELP; >> >> >> >> >> >> >> >> >> >> Error: ERROR 504 (42703): Undefined column. columnName=EBELN >> (state=42703,code=504) >> >> org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): >> Undefined column. columnName=EBELN >> >> at org.apache.phoenix.schema.PTab >> leImpl.getColumn(PTableImpl.java:671) >> >> at org.apache.phoenix.compile.Fro >> mCompiler$MultiTableColumnResolver.resolveColumn(FromCompiler.java:874) >> >> at org.apache.phoenix.compile.Joi >> nCompiler$ColumnRefParseNodeVisitor.visit(JoinCompiler.java:1003) >> >> at org.apache.phoenix.compile.Joi >> nCompiler$ColumnRefParseNodeVisitor.visit(JoinCompiler.java:979) >> >> at org.apache.phoenix.parse.Colum >> nParseNode.accept(ColumnParseNode.java:56) >> >> at org.apache.phoenix.compile.Joi >> nCompiler$OnNodeVisitor.visitLeave(JoinCompiler.java:932) >> >> at org.apache.phoenix.compile.Joi >> nCompiler$OnNodeVisitor.visitLeave(JoinCompiler.java:870) >> >> at org.apache.phoenix.parse.Compa >> risonParseNode.accept(ComparisonParseNode.java:47) >> >> at org.apache.phoenix.parse.Compo >> undParseNode.acceptChildren(CompoundParseNode.java:64) >> >> >> >> >> >> >> >> >> >> Thanks, >> >> Ryan >> >> >> > >
