All, so I am trying to run the query. The query consist of 7 tables and I want to all table get left joined based on A.conn_keyy and the others with clause 'ON'
My confusion comes when I want to join CPLCUR based on A , not works.
(CPLCUR.conn_keyy in ( a.conn_keyy = b.conn_keyy )
It appears error : both left and right aliases encountered in join 'conn_key'
set hive.support.quoted.identifiers=none;
select
coalesce(a.conn_keyy, b.conn_keyy,CPLCUR.conn_keyy) as rrconn_keyy,
b.rfbbn, b.LINES_ID,b.TYPE,CPLCUR.*
FROM
(tablee.aa)A
LEFT OUTER JOIN
(tablee.bb) B
ON (A.conn_keyy = B.conn_keyy)
LEFT OUTER JOIN (SELECT `(c21)?+.+` FROM tablee.cc ) CPLCUR
ON (CPLCUR.conn_keyy in ( a.conn_keyy = b.conn_keyy )
AND CPLCUR.cllt = REGEXP_EXTRACT(B.rfbbn,'^(?:[^*]*\\*){2}([^*]*)',1))
LEFT OUTER JOIN (SELECT DISTINCT * FROM tablee.dd) CPLBAL
ON CPLBAL.conn_keyy = A.conn_keyy
AND CPLBAL.SEQUENCE = CPLCUR.SEQUENCE
AND CPLBAL.dtdt = '1999'
LEFT OUTER JOIN
(tablee.REP)REP
ON REP.relino = B.lnido
LEFT OUTER JOIN tablee.P PRD
ON PRD.PRODUCT_CODE = REGEXP_EXTRACT(A.conn_keyy,'[.]([^.]+)',1)
AND PRD.dtdt = '1999'
WHERE B.lnido LIKE 'PLCONS1%'
) rrvv;
What is best practice to get this?
The desired results:
+-----------+---------+--------+----------+-------------+-------+-----+-----+
| conn_keyy | b.rfbbn | b.LINES| b.TYPE | CPLCUR | CPLBAL| REP | PRD |
+-----------+---------+--------+----------+-------------+-------+-----+-----+
| 111 | aaa | PCOS1% | bbsr | 2019-02-21 | | | |
| 200 | | PCOS1% | ny | X | | | |
| 222 | bbb | PCOS1% | pp | Y | | | |
| 300 | rrr | PCOS1% | atl | 2019-03-18 | | | |
| 333 | ccc | PCOS1% | dd | Z | | | |
| 400 | vvv | PCOS1% | tt | 2019-03-18 | | | |
+-----------+---------+--------+----------+-------------+-------+-----+-----+
