phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Maryann Xue <maryann....@gmail.com>
Subject Re: Phoenix join on derived table and documentation
Date Fri, 22 Sep 2017 18:22:38 GMT
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
>
>
>

Mime
View raw message