Regarding resident statement, temporary tables and join.

[#1]

Hi all, i have a question here, i have to load some calculated fields from a temporary table and then left join with the same temporary table, this is posible??

I have my script here, but it's giving me the 'invalid expression' error. Could someone help me with this?

LEFT JOIN (Int_Actividades_TMP)

LOAD DISTINCT(ProcessInstanceId) AS ProcessInstanceId,
MAX(EstadosActividades_FechaCreacion) AS ÚltimaActividadFechaCreación,
Teco_Group_Desc AS ÚltimaActividadGrupo,
NombreActividad AS ÚltimaActividadNombre
RESIDENT Int_Actividades_TMP
GROUP BY ProcessInstanceId

Thank you very much.

[#2]

I've just realized that i was missing the other fields in the group by clause.

Problem solve.

[#3]

Yes, this is possible.

Test the follow code:

LEFT JOIN (Int_Actividades_TMP)

LOAD DISTINCT(ProcessInstanceId) AS ProcessInstanceId,
MAX(EstadosActividades_FechaCreacion) AS ÚltimaActividadFechaCreación,
Teco_Group_Desc AS ÚltimaActividadGrupo,
NombreActividad AS ÚltimaActividadNombre
RESIDENT Int_Actividades_TMP
GROUP BY ProcessInstanceId, Teco_Group_Desc, NombreActividad;

You must have all fiels without function in the Group-By-Clause.

[#4]

Yes, thanks for the response, now i have another problem:

EstadosActividades_FechaCreacion its a date field.

when i want to see the max(EstadosActividades_FechaCreacion) data, i don't see a date field, only numbers, i tried with : date (max(EstadosActividades_FechaCreacion), 'DD/MM/YY') or max(date(EstadosActividades_FechaCreacion, 'DD/MM/YY')) and the format it's ok, but the numbers are incoheren, for example it shows: 29/50/08 on the max(EstadosActividades_FechaCreacion) field.

[#5]

I don´t know.

The version

date (max(EstadosActividades_FechaCreacion), 'DD/MM/YY')

works at my example correct.