inline sql

[luciffer]

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_action))

FROM tracking_problem trprblm
,tracking_problem_action trpact
,action_status_type actststp
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.

Can anyone suggest a better way?

[mallika]

use nls_sort feature to get a desired result.

[luciffer]

can you explain how the nls_sort feature would apply?