Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Unanswered: ORA-01799 on translated MSSQL query

    Hi Guru's

    Bit of a novice and require some assistance.

    I have an MSSQL query which works fine with my tables and returns the data correctly, but when I try to translate it, Oracle comes back with the dreaded ORA-01799.

    The MS-SQL code is attached as is the translated oracle code (my attempt at least).

    If you could point me to how to re-write the query to make oracle happy and give me the results I need it would certainly be appreciated.

    Regards
    Dave Morgan
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool HIerarchical query

    What you are looking for is a hierarchical query.
    Oracle has the ability to navigate this type of structure more easily than with MSSQL, here is an example (highlighted in red):
    Code:
    WITH CTM_People AS (
    SELECT 001             Person_ID
         , 'USR001'         Remedy_Login_ID
         , 'CEO001'         ManagerLoginID
         , 'A'             Client_Type
         , 'Indiana'         First_Name
         , 'Jones'          Last_Name
         , 'ijones@myplace.com'    Internet_E_mail
      FROM DUAL UNION ALL
    SELECT 010, 'USR010', 'USR001', 'B', 'Jeremy' ,'Smith'  , 'jsmith@myplace.com'  FROM DUAL UNION ALL
    SELECT 012, 'USR012', 'USR010', 'B', 'Sam'    ,'Addams' , 'saddams@myplace.com' FROM DUAL UNION ALL
    SELECT 013, 'USR013', 'USR012', 'B', 'Annie'  ,'Brown'  , 'abrown@myplace.com'  FROM DUAL UNION ALL
    SELECT 020, 'USR020', 'USR013', 'C', 'Bonnie' ,'Clyde'  , 'bclyde@myplace.com'  FROM DUAL UNION ALL
    SELECT 021, 'USR021', 'USR020', 'C', 'Carlos' ,'Piedra' , 'cpiedra@myplace.com' FROM DUAL UNION ALL
    SELECT 022, 'USR022', 'USR021', 'C', 'Peter'  ,'Pan'    , 'ppan@myplace.com'    FROM DUAL UNION ALL
    SELECT 030, 'USR030', 'USR022', 'D', 'Pamela' ,'Kraut'  , 'pkraut@myplace.com'  FROM DUAL UNION ALL
    SELECT 033, 'USR033', 'USR030', 'D', 'Kim'    ,'Dawne'  , 'kdawne@myplace.com'  FROM DUAL UNION ALL
    SELECT 040, 'USR040', 'USR033', 'D', 'Don'    ,'Juan'   , 'djuan@myplace.com'   FROM DUAL UNION ALL
    SELECT 044, 'USR044', 'USR040', 'E', 'Joe'    ,'Moe'    , 'jmoe@myplace.com'    FROM DUAL UNION ALL
    SELECT 050, 'USR050', 'USR040', 'E', 'Mike'   ,'Smart'  , 'msmart@myplace.com'  FROM DUAL UNION ALL
    SELECT 100, 'USR100', 'USR040', 'E', 'Elka'   ,'Brown'  , 'ebrown@myplace.com'  FROM DUAL)
    SELECT lpad(level,level+1), q.*
      FROM CTM_People q
     START WITH Person_Id = 100
     CONNECT BY PRIOR  ManagerLoginID = Remedy_Login_ID;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2010
    Posts
    2

    Thumbs up RE: ORA-01799 on translated MSSQL query

    Hi LKBrwn_DBA




    Many thanks for the query construct. I should now be able to create the view that a colleague wanted which will trawl the contents of the table to track-back the managers :-)

    Its lovely and short compared to MSSQL too

    Regards
    Dave Morgan

Posting Permissions

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