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.PTableImpl.getColumn( > PTableImpl.java:671) > > at org.apache.phoenix.compile.FromCompiler$ > MultiTableColumnResolver.resolveColumn(FromCompiler.java:874) > > at org.apache.phoenix.compile.JoinCompiler$ > ColumnRefParseNodeVisitor.visit(JoinCompiler.java:1003) > > at org.apache.phoenix.compile.JoinCompiler$ > ColumnRefParseNodeVisitor.visit(JoinCompiler.java:979) > > at org.apache.phoenix.parse.ColumnParseNode.accept( > ColumnParseNode.java:56) > > at org.apache.phoenix.compile.JoinCompiler$OnNodeVisitor. > visitLeave(JoinCompiler.java:932) > > at org.apache.phoenix.compile.JoinCompiler$OnNodeVisitor. > visitLeave(JoinCompiler.java:870) > > at org.apache.phoenix.parse.ComparisonParseNode.accept( > ComparisonParseNode.java:47) > > at org.apache.phoenix.parse.CompoundParseNode. > acceptChildren(CompoundParseNode.java:64) > > > > > > > > > > Thanks, > > Ryan > > >
