Unable to load data having max date

[P_Kale]

Hi,

PTRN_2:
Load
CHDRNUM,
DayName(PTRNEFF) as PTRNEFF_NEW,
date(date#(TRANSACTION_DATE,'YYMMDD'),'DD/MM/YYYY') as TRANSACTION_DATE,
TRANSACTION_TIME,
BATCTRCDE,
VALIDFLAG

Resident PTRN_1 Where VALIDFLAG <> 2 and DayName(PTRNEFF) >='01/11/2023' and DayName(PTRNEFF) <='30/11/2023'
and date(date#(TRANSACTION_DATE,'YYMMDD'),'DD/MM/YYYY') >= '01/11/2023';
//Drop Table PTRN_1;

Inner Join

Load
CHDRNUM,
MAX(date(date#(TRANSACTION_DATE,'YYMMDD'),'DD/MM/YYYY')) as TRANSACTION_DATE
Resident PTRN_1;

Drop Table PTRN_1;

I am trying to load data where policies having max Transaction_date. But I am getting below attached error. Can anyone help to get it resolved.

Thanks in advance.

[jchx]

Hello P_Kale,

You are missing a group by clause for an aggregate max function present in your load script. Try this:

Load
CHDRNUM,
MAX(date(date#(TRANSACTION_DATE,'YYMMDD'),'DD/MM/YYYY')) as TRANSACTION_DATE
Resident PTRN_1
Group by CHDRNUM;

[P_Kale]

Thanks