I am facing
*Error: ERROR 502 (42702): Column reference ambiguous or duplicate names.
columnName=EARNINGS*
*SQLState: 42702*
*ErrorCode: 502*
while running below query.
select SUM (TOTAL_PAYMENTS)
from ( select P_1.AGENT_ID
,PAYMENTS
,case when P_1.PAYMENTS < 10000 then 10000 - P_1.PAYMENTS
else 0 end DRAWS
,case when P_1.PAYMENTS > 10000 then P_1.PAYMENTS else
10000 end TOTAL_PAYMENTS
from ( select COMPS.AGENT_ID
,COMPS.TOTAL_EARNINGS +
COALESCE(ADJ.ADJUSTMENT, 0) as PAYMENTS
from ( select COMPS_1.AGENT_ID
,SUM(COMPS_1.COMP_WEIGHT *
COMPS_1.EARNINGS / 100) as TOTAL_EARNINGS
from ( select AGENT_ID, EARNINGS, 70
as COMP_WEIGHT
from ( select C1_2.AGENT_ID
,COALESCE(SUM(C1_2.EARNINGS), 0) as EARNINGS
from ( select
C1_1.AGENT_ID
,C1_1.TERRITORY_ID
,C1_1.PRODUCT_ID
,C1_1.EARNINGS_PERCENT
,LK.RESULT_VALUE as EARNINGS
from (
select M1.AGENT_ID
,M1.TERRITORY_ID
,M1.PRODUCT_ID
,(M1.METRIC * 100) / TG.SALES_TARGET as EARNINGS_PERCENT
from
( select T.AGENT_ID
,T.TERRITORY_ID
,T.PRODUCT_ID
,SUM(T.TOTAL_SALES - T.TOTAL_EXPENSES) as METRIC
from TRANSACTIONS T
group by T.AGENT_ID, T.TERRITORY_ID, T.PRODUCT_ID
) M1
left join PRODUCT_TERR_TARGETS TG on M1.TERRITORY_ID = TG.TERRITORY_ID
and M1.PRODUCT_ID = TG.PRODUCT_ID
) C1_1
left
join PRODUCT_EARNINGS_LOOKUP LK on C1_1.PRODUCT_ID = LK.PRODUCT_ID
and C1_1.EARNINGS_PERCENT >= LK.LOWER_VALUE
and C1_1.EARNINGS_PERCENT <
COALESCE(LK.UPPER_VALUE, 1e38)
) C1_2
group by C1_2.AGENT_ID
) C1
union all
select AGENT_ID, EARNINGS, 20
as COMP_WEIGHT
from ( select C2_1.AGENT_ID
,COALESCE(SUM(C2_1.EARNINGS), 0) as EARNINGS
from ( select
M2.AGENT_ID
,M2.PRODUCT_ID
,M2.METRIC * PCC.COMM_VALUE as EARNINGS
from (
select T.AGENT_ID
,T.PRODUCT_ID
,SUM(T.UNIT_CNT_SOLD) as METRIC
from TRANSACTIONS T
group by T.AGENT_ID, T.PRODUCT_ID
) M2
left
join PRODUCTS P on M2.PRODUCT_ID = P.P_ID
left
join PRODUCT_CATEGORY_COMMISSIONS PCC on P.P_CATEGORY = PCC.PRODUCT_CATEGORY
) C2_1
group by C2_1.AGENT_ID
) C2
union all
select AGENT_ID, EARNINGS, 10
as COMP_WEIGHT
from ( select AGENT_ID
,COALESCE(EARNINGS, 0) as EARNINGS
from ( select
M3.AGENT_ID
,LK.RESULT_VALUE as EARNINGS
from (
select T.AGENT_ID
,COUNT(T.PRODUCT_ID) as METRIC
from TRANSACTIONS T
group by T.AGENT_ID
) M3
left
join AGENTS A on M3.AGENT_ID = A.A_ID
left
join ROLE_DEAL_COUNT_LOOKUP LK on A.A_ROLE = LK.AGENT_ROLE
and M3.METRIC >= LK.LOWER_VALUE
and M3.METRIC < COALESCE(LK.UPPER_VALUE, 1e38)
) C3_1
) C3
) COMPS_1
group by COMPS_1.AGENT_ID
) COMPS
left join AGENT_PAY_ADJUSTMENTS ADJ on
COMPS.AGENT_ID = ADJ.AGENT_ID
) P_1
) P;
PS:
There is below stackoverflow thread -
http://stackoverflow.com/questions/34450534/column-reference-ambiguous-or-duplicate-names-on-apache-phoenix-when-using-ali
also that is unanswered.
--
*With Best Regards,*
* Mohd Adnan*
* Feature Development Lead*
* Optymyze Inc*
*Mobile +91-7498194516 *
* Blog adnanfaizan.blogspot.in
<http://adnanfaizan.blogspot.in>*
