phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: Phoenix join on derived table and documentation
Date Tue, 26 Sep 2017 04:24:03 GMT
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> 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> 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.PTab
>> leImpl.getColumn(PTableImpl.java:671)
>>
>>                 at org.apache.phoenix.compile.Fro
>> mCompiler$MultiTableColumnResolver.resolveColumn(FromCompiler.java:874)
>>
>>                 at org.apache.phoenix.compile.Joi
>> nCompiler$ColumnRefParseNodeVisitor.visit(JoinCompiler.java:1003)
>>
>>                 at org.apache.phoenix.compile.Joi
>> nCompiler$ColumnRefParseNodeVisitor.visit(JoinCompiler.java:979)
>>
>>                 at org.apache.phoenix.parse.Colum
>> nParseNode.accept(ColumnParseNode.java:56)
>>
>>                 at org.apache.phoenix.compile.Joi
>> nCompiler$OnNodeVisitor.visitLeave(JoinCompiler.java:932)
>>
>>                 at org.apache.phoenix.compile.Joi
>> nCompiler$OnNodeVisitor.visitLeave(JoinCompiler.java:870)
>>
>>                 at org.apache.phoenix.parse.Compa
>> risonParseNode.accept(ComparisonParseNode.java:47)
>>
>>                 at org.apache.phoenix.parse.Compo
>> undParseNode.acceptChildren(CompoundParseNode.java:64)
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> Thanks,
>>
>> Ryan
>>
>>
>>
>
>

Mime
View raw message