Sorry, forgot to attach the ddl for the tables being used…
From: Ryan Templeton <rtemple...@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
CREATE TABLE IF NOT EXISTS EKKO( MANDT VARCHAR, EBELN VARCHAR NOT NULL, BUKRS VARCHAR, BSTYP VARCHAR, BSART VARCHAR, BSAKZ VARCHAR, LOEKZ VARCHAR, STATU VARCHAR, AEDAT DATE, ERNAM VARCHAR, PINCR VARCHAR, LPONR VARCHAR, LIFNR VARCHAR, SPRAS VARCHAR, ZTERM VARCHAR, ZBD1T INTEGER, ZBD2T INTEGER, ZBD3T INTEGER, ZBD1P FLOAT, ZBD2P FLOAT, EKORG VARCHAR, EKGRP VARCHAR, WAERS VARCHAR, WKURS FLOAT, KUFIX VARCHAR, BEDAT DATE, KDATB DATE, KDATE DATE, BWBDT DATE, ANGDT DATE, BNDDT DATE, GWLDT DATE, AUSNR VARCHAR, ANGNR VARCHAR, IHRAN DATE, IHREZ VARCHAR, VERKF VARCHAR, TELF1 VARCHAR, LLIEF VARCHAR, KUNNR VARCHAR, KONNR VARCHAR, ABGRU VARCHAR, AUTLF VARCHAR, WEAKT VARCHAR, RESWK VARCHAR, LBLIF VARCHAR, INCO1 VARCHAR, INCO2 VARCHAR, KTWRT FLOAT, SUBMI VARCHAR, KNUMV VARCHAR, KALSM VARCHAR, STAFO VARCHAR, LIFRE VARCHAR, EXNUM VARCHAR, UNSEZ VARCHAR, LOGSY VARCHAR, UPINC VARCHAR, STAKO VARCHAR, FRGGR VARCHAR, FRGSX VARCHAR, FRGKE VARCHAR, FRGZU VARCHAR, FRGRL VARCHAR, LANDS VARCHAR, LPHIS VARCHAR, ADRNR VARCHAR, STCEG_L VARCHAR, STCEG VARCHAR, ABSGR VARCHAR, ADDNR VARCHAR, KORNR VARCHAR, MEMORY VARCHAR, PROCSTAT VARCHAR, RLWRT FLOAT, REVNO VARCHAR, SCMPROC VARCHAR, REASON_CODE VARCHAR, MEMORYTYPE VARCHAR, RETTP VARCHAR, RETPC FLOAT, DPTYP VARCHAR, DPPCT FLOAT, DPAMT FLOAT, DPDAT DATE, MSR_ID VARCHAR, HIERARCHY_EXISTS VARCHAR, THRESHOLD_EXISTS VARCHAR, LEGAL_CONTRACT VARCHAR, DESCRIPTION VARCHAR, RELEASE_DATE DATE, VSART VARCHAR, HANDOVERLOC VARCHAR, SHIPCOND VARCHAR, INCOV VARCHAR, INCO2_L VARCHAR, INCO3_L VARCHAR, FORCE_ID VARCHAR, FORCE_CNT VARCHAR, RELOC_ID VARCHAR, RELOC_SEQ_ID VARCHAR, SOURCE_LOGSYS VARCHAR, FSH_TRANSACTION VARCHAR, FSH_ITEM_GROUP VARCHAR, FSH_VAS_LAST_ITEM VARCHAR, FSH_OS_STG_CHANGE VARCHAR, VZSKZ VARCHAR, FSH_SNST_STATUS VARCHAR, POHF_TYPE VARCHAR, EQ_EINDT DATE, EQ_WERKS VARCHAR, FIXPO VARCHAR, EKGRP_ALLOW VARCHAR, WERKS_ALLOW VARCHAR, CONTRACT_ALLOW VARCHAR, PSTYP_ALLOW VARCHAR, FIXPO_ALLOW VARCHAR, KEY_ID_ALLOW VARCHAR, AUREL_ALLOW VARCHAR, DELPER_ALLOW VARCHAR, EINDT_ALLOW VARCHAR, LTSNR_ALLOW VARCHAR, OTB_LEVEL VARCHAR, OTB_COND_TYPE VARCHAR, KEY_ID VARCHAR, OTB_VALUE FLOAT, OTB_CURR VARCHAR, OTB_RES_VALUE FLOAT, OTB_SPEC_VALUE FLOAT, SPR_RSN_PROFILE VARCHAR, BUDG_TYPE VARCHAR, OTB_STATUS VARCHAR, OTB_REASON VARCHAR, CHECK_TYPE VARCHAR, CON_OTB_REQ VARCHAR, CON_PREBOOK_LEV VARCHAR, CON_DISTR_LEV VARCHAR, ATT_REP_TIME VARCHAR, constraint pk primary key(EBELN) ); CREATE TABLE EKBE( MANDT VARCHAR, EBELN VARCHAR, EBELP VARCHAR, ZEKKN VARCHAR, VGABE VARCHAR, GJAHR VARCHAR, BELNR VARCHAR, BUZEI VARCHAR, BEWTP VARCHAR, BWART VARCHAR, BUDAT DATE, MENGE FLOAT, BPMNG FLOAT, DMBTR FLOAT, WRBTR FLOAT, WAERS VARCHAR, AREWR FLOAT, WESBS FLOAT, BPWES FLOAT, SHKZG VARCHAR, BWTAR VARCHAR, ELIKZ VARCHAR, XBLNR VARCHAR, LFGJA VARCHAR, LFBNR VARCHAR, LFPOS VARCHAR, GRUND VARCHAR, CPUDT DATE, CPUTM TIMESTAMP, REEWR FLOAT, EVERE VARCHAR, REFWR FLOAT, MATNR VARCHAR, WERKS VARCHAR, XWSBR VARCHAR, ETENS VARCHAR, KNUMV VARCHAR, MWSKZ VARCHAR, LSMNG FLOAT, LSMEH VARCHAR, EMATN VARCHAR, AREWW FLOAT, HSWAE VARCHAR, BAMNG FLOAT, CHARG VARCHAR, BLDAT DATE, XWOFF VARCHAR, XUNPL VARCHAR, ERNAM VARCHAR, SRVPOS VARCHAR, PACKNO VARCHAR, INTROW VARCHAR, BEKKN VARCHAR, LEMIN VARCHAR, AREWB FLOAT, REWRB FLOAT, SAPRL VARCHAR, MENGE_POP FLOAT, BPMNG_POP FLOAT, DMBTR_POP FLOAT, WRBTR_POP FLOAT, WESBB FLOAT, BPWEB FLOAT, WEORA VARCHAR, AREWR_POP FLOAT, KUDIF FLOAT, RETAMT_FC FLOAT, RETAMT_LC FLOAT, RETAMTP_FC FLOAT, RETAMTP_LC FLOAT, XMACC VARCHAR, WKURS FLOAT, INV_ITEM_ORIGIN VARCHAR, VBELN_ST VARCHAR, VBELP_ST VARCHAR, SGT_SCAT VARCHAR, ET_UPD VARCHAR, CWM_BAMNG FLOAT, CWM_WESBS FLOAT, CWM_TY2TQ VARCHAR, CWM_WESBB FLOAT, J_SC_DIE_COMP_F VARCHAR, FSH_SEASON_YEAR VARCHAR, FSH_SEASON VARCHAR, FSH_COLLECTION VARCHAR, FSH_THEME VARCHAR, WRF_CHARSTC1 VARCHAR, WRF_CHARSTC2 VARCHAR, WRF_CHARSTC3 VARCHAR, ATT_REP_TIME VARCHAR, constraint pk primary key(EBELN, EBELP));
