DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: inline sql

  1. #1
    Join Date
    Dec 2003

    inline sql

    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?

  2. #2
    Join Date
    May 2002
    use nls_sort feature to get a desired result.

  3. #3
    Join Date
    Dec 2003
    can you explain how the nls_sort feature would apply?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.