Thank you, this works! From: Maryann Xue <[email protected]> Reply-To: "[email protected]" <[email protected]> Date: Friday, September 22, 2017 at 1:23 PM To: "[email protected]" <[email protected]> Subject: Re: Phoenix join on derived table and documentation
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 <[email protected]<mailto:[email protected]>> wrote: Sorry, forgot to attach the ddl for the tables being used… From: Ryan Templeton <[email protected]<mailto:[email protected]>> Reply-To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Date: Friday, September 22, 2017 at 9:54 AM To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> 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
