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

Thread: inline sql

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    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
    Posts
    27
    use nls_sort feature to get a desired result.

  3. #3
    Join Date
    Dec 2003
    Posts
    90
    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