phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ryan Templeton <rtemple...@hortonworks.com>
Subject Re: Phoenix join on derived table and documentation
Date Tue, 26 Sep 2017 18:08:39 GMT
Sorry for the delay, just tried it and it works. Thanks!

From: James Taylor <jamestaylor@apache.org>
Reply-To: "user@phoenix.apache.org" <user@phoenix.apache.org>
Date: Monday, September 25, 2017 at 11:24 PM
To: user <user@phoenix.apache.org>
Subject: Re: Phoenix join on derived table and documentation

Ryan - did the work around solve the issue?

Thanks,
James

On Fri, Sep 22, 2017 at 11:22 AM, Maryann Xue <maryann.xue@gmail.com<mailto:maryann.xue@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 <rtempleton@hortonworks.com<mailto:rtempleton@hortonworks.com>>
wrote:
Sorry, forgot to attach the ddl for the tables being used…



From: Ryan Templeton <rtempleton@hortonworks.com<mailto:rtempleton@hortonworks.com>>
Reply-To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" <user@phoenix.apache.org<mailto:user@phoenix.apache.org>>
Date: Friday, September 22, 2017 at 9:54 AM
To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" <user@phoenix.apache.org<mailto: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



Mime
View raw message