Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
im trying to create a flag that will store the most recent record for a specific field.
i am using the"unique record ID(f_sgrp) as my max, then just grouping it by LINEID. however im dealing with an oracle SQL database and it doesn't seem to allow me to do this.
my mapping is a simple:
mapping
load f_prcs, //this is my lineID
max(f_SGRP) //this is my unique record ID
;
sql select f_prcs, f_sgrp
from X
WHERE Y
group by f_prcs
in my log file it is saying
"Error: Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 979, ErrorMsg: [Oracle][ODBC][Ora]ORA-00979: not a GROUP BY expression" i think this is because for oracle SQL you have to group by both select statements?
however when i try that, i get the error message:
Error: Field 'f_SGRP' not found
any ideas on how to correct this?
hi @stevejoyce , thanks for that,
i got it working by using:
mapping
sql select f_prcs, max(f_sgrp) from X
where
Y
group by f_prcs
Your group by should be in the same select statement of your aggregation. What if you try:
mapping
load f_prcs, //this is my lineID
max(f_SGRP) //this is my unique record ID
group by f_prcs
;
sql select f_prcs, f_sgrp
from X
WHERE Y
hi @stevejoyce , thanks for that,
i got it working by using:
mapping
sql select f_prcs, max(f_sgrp) from X
where
Y
group by f_prcs