Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Can someone please explain to me why this is creating an error
left join (temp1)
LOAD HIN,
Channel,
left([Trans_Date],6) as Period,
count([Trans_Date]) as Transactions,
sum(if([Transaction_type]='Withdrawal' and [ResultCode]=0,1,0)) as Withdrawels,
sum(if([Transaction_type]<>'Withdrawal',1,0)) as Balance_Enquiries,
sum(if([Transaction_type]='Withdrawal' and [ResultCode]=0,[CompletionAmount],0)) as Withdrawel_Amount
resident NPS_ATM;
Hi, you have Sum's and Count's, those funtions needs a group by clause by any other field not included in these functions, this is:
...
resident NPS_ATM
Group by HIN, Channel, Period;
Hi, you have Sum's and Count's, those funtions needs a group by clause by any other field not included in these functions, this is:
...
resident NPS_ATM
Group by HIN, Channel, Period;
Thanks...I just earned the blonde award of the day
you use a grouping function (sum) without group
please check if the sum is necessary
Maybe it is easier to define a temporary table using Group by
and then join the table
TEMP:
LOAD HIN,
Channel,
left([Trans_Date],6) as Period,
count([Trans_Date]) as Transactions,
sum(if([Transaction_type]='Withdrawal' and [ResultCode]=0,1,0)) as Withdrawels,
sum(if([Transaction_type]<>'Withdrawal',1,0)) as Balance_Enquiries,
sum(if([Transaction_type]='Withdrawal' and [ResultCode]=0,[CompletionAmount],0)) as Withdrawel_Amount
resident NPS_ATM
Group by HIN, Channel
left join(temp1)
load *
resident TEMP;
drop table TEMP;