Thank you, this works!

 

From: Maryann Xue <maryann.xue@gmail.com>
Reply-To: "user@phoenix.apache.org" <user@phoenix.apache.org>
Date: Friday, September 22, 2017 at 1:23 PM
To: "user@phoenix.apache.org" <user@phoenix.apache.org>
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 <rtempleton@hortonworks.com> wrote:

Sorry, forgot to attach the ddl for the tables being used…

 

 

 

From: Ryan Templeton <rtempleton@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