Tables not joinging properly [Error 10004]

Question

Select count(*) cnt
from fin_sap_prd_tbls.cdhdr cdhdr
  Join fin_sap_prd_tbls.cdpos cdpos on (cdhdr.changenr = cdpos.changenr)
  Join fin_sap_prd_tbls.ekko  ekko on (ekko.lifnr = cdpos.objectid)
  Join fin_sap_prd_tbls.ekpo  ekpo on (ekpo.ebeln = ekko.ebeln)
where cdhdr.objectclas = 'KRED'
  and cdhdr.objectid = 'vendornumber'
  and cdpos.fname = 'ZTERM'
  and ekpo.elikz != 'X';


Select count(*) cnt
from fin_sap_prd_tbls.cdhdr xcdhdr
  Join fin_sap_prd_tbls.cdpos xcdpos on (xcdhdr.changenr = xcdpos.changenr)
 /* Join fin_sap_prd_tbls.ekko  xekko on (xekko.lifnr = xcdpos.objectid)*/
 /* Join fin_sap_prd_tbls.ekpo  xekpo on (xekpo.ebeln = xekko.ebeln)*/
  where xcdhdr.objectclas = 'KRED'
  and xcdhdr.objectid = 'vendornumber'
  and xcdpos.fname = 'ZTERM'
  /*and xekpo.elikz != 'X';*/

Select count(*) cnt
from /*fin_sap_prd_tbls.cdhdr xcdhdr
  Join*/ fin_sap_prd_tbls.cdpos xcdpos /*on (xcdhdr.changenr = xcdpos.changenr)*/
  Join fin_sap_prd_tbls.ekko  xekko on (xekko.lifnr = xcdpos.objectid)
 /* Join fin_sap_prd_tbls.ekpo  xekpo on (xekpo.ebeln = xekko.ebeln)*/
  where xcdhdr.objectclas = 'KRED'
  and xcdhdr.objectid = 'vendornumber'
  and xcdpos.fname = 'ZTERM'
  /*and xekpo.elikz != 'X';*/

The data base I am using is Hive, and I get the below error when I run this code. When I run all my joins separately everything works fine and data is returned.

As a side question I also would like the 'hdr' table there can be multiple results I would like it to produce the most recent change. I have tried the Max() but it errors out as well.

FAILED: SemanticException [Error 10004]: Line 1:10155 Invalid table alias or column reference 'cdpos'

Why do you need the aliases at all the table names match your alias. — xQbert
Because I do not want to write out the whole location every time. I have multiple choices for 'fin_sap_prd_tbls.' and I have to qualify it. — bkirby
The exact code, I copied and pasted it. only change I made was I removed the true vendor number and replaced it with "vendor number" — bkirby

xQbert xQbert · Accepted Answer · 2017-02-28T17:27:24

I'd remove the alias names as the table names already match the alias. The engine might be confused by having objects with the same name.

SELECT count(*) cnt
FROM fin_vsap_prd_tbls.cdhdr 
  JOIN fin_sap_prd_tbls.cdpos on (cdhdr.changenr = cdpos.changenr)
  JOIN fin_sap_prd_tbls.ekko  on (ekko.lifnr = cdpos.objectid)
  JOIN fin_sap_prd_tbls.ekpo  on (ekpo.ebeln = ekko.ebeln)
WHERE cdhdr.objectclas = 'KRED'
  and cdhdr.objectid = 'vendornumber'
  and cdpos.fname = 'ZTERM'
  and ekpo.elikz != 'X';

or name them differently...

SELECT count(*) cnt
FROM fin_vsap_prd_tbls.cdhdr  xcdhdr
  JOIN fin_sap_prd_tbls.cdpos xcdpos on (xcdhdr.changenr = xcdpos.changenr)
  JOIN fin_sap_prd_tbls.ekko  xekko on (xekko.lifnr = xcdpos.objectid)
  JOIN fin_sap_prd_tbls.ekpo  xekpo on (xekpo.ebeln = xekko.ebeln)
WHERE xcdhdr.objectclas = 'KRED'
  and xcdhdr.objectid = 'vendornumber'
  and xcdpos.fname = 'ZTERM'
  and xekpo.elikz != 'X';

Tables not joinging properly [Error 10004]

1 Answers