I have three tables: Problems, Actions, Action_Status_Types.
There is a trigger on the Problems table that creates an Action with status = Open every time a Problem is created.
Each user can have multiple problems and each problem can have multiple actions.
I want to get all the problems for a given user and I want to display the status of a problem as the status of the latest(by date) action for that problem.
The name of the status needs to come from the Action_Status_Types table and I need the english and french names from that table.
I am currently doing this
SELECT DISTINCT trprblm.id AS id
, trprblm.problem_desc AS problem_desc
, max(NVL(trprblm.date_updated,trprblm.date_created)) AS problem_date
, (SELECT action_status_type.action_status_type_name_en
FROM tracking_problem_action, action_status_type
WHERE tracking_problem_action.TRPRBLM__ID = 13
AND tracking_problem_action.ACTSTSTP__ID = action_status_type.ID
AND tracking_problem_action.DATE_UPDATED = (SELECT Max(NVL(date_updated,date_created))
FROM tracking_problem trprblm
WHERE trprblm.id = trpact.trprblm__id
AND actststp.id = trpact.actststp__id
AND prsn.ID = pi_prsn_id
ORDER BY problem_date desc;
But my inline sql gets only the english Action_Status_Type name and I would have to repeat that query to get the french status name.