Could you please file a bug? A workaround is to not use the same column name with different column family.
Thanks, Sergey On Wed, Mar 2, 2016 at 3:52 AM, Mohammad Adnan Raza <[email protected]> wrote: > 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>*
