Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Need a little help in converting Oracle SQL (Access 2003)

    I have a short SQL statement that I would like to use in Access, but I am not quite sure of the Access SQL equivalent.

    select * from EMPLOYEE_TABLE start with USER_ID in ('Bubba','Babble') connect by SUPV_ID = prior USER_ID

    I am not sure how to code the START WITH and CONNECT BY portions of the SQL. I do have a not so elegant way of doing this by joining the same table in a downward string 11 times & takes 4 queries to give me the desired output. (to cover all bases, I need to expand this to 3 more table listings)


    Basically what this does is gives me the Employee_Table listing all employees that would roll up to one of the supervisors listed in the IN statement. As there can be many levels of management between a top tier manager & the lowest worker bee (and dummy roll up ID's for large orgs), the SQL sample I listed above is much easier to maintain & I think accurate then what I slapped together in Access.

    So if anyone can provide me some guidance, that would be much appreciated!


    PS: This is currently is being run against a Access table.
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need a little help in converting Oracle SQL (Access 2003)

    The START WITH ... CONNECT BY construct in Oracle SQL provides recursion. There is no equivalent for this in Jet SQL, so you must either resort to joining the table to itself as many times as the "deepest" hierarchichal relation requires, or use VBA - you could write a recursive function that returns the top manager for an employee and use that in your query.

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need a little help in converting Oracle SQL (Access 2003)

    Thanks Hans.

    I guess I should re-state my goal. To get a list of employees that eventually map up to the person(s) listed in the IN statement.

    So in my example, there are 300 employees that eventually map up to Bubba & 400 up to Babble. So what I want to return is a list of 700 employee id's. (then use that list to limit other queries)

    May need work on creating a recursive VBA function as you suggest. (yeeach) At least there are only 20K +/- or so records in the table.
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need a little help in converting Oracle SQL (Access 2003)

    Let us know if you need help with the recursive function. It'll be slow, though. Why not leave the whole thing in Oracle? The START WITH ... CONNECT BY construct is much more convenient than any solution in Access.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need a little help in converting Oracle SQL (

    Hi there

    Without the table structure I have taken the liberty of doing a little demo and you may be able to get some ideas from it. On first reading this I have made the assumption that the Oracle table had a Self Join, something that Access is unable to do. You can use the SQL Editor to create temporary tables giving them pseudonyms to assist syntax. I have have a habit of using Oracle syntax as this was what I was first taught and I prefer it and it works very well in MS Access <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    The image below depicts an employee table with who reports to who. The SQL selects the unique staff records from tblEmployees calls it a and then creates a temp table with the same content called b then matches the staff against manager. This is a simplified example but has very powerful possibilities but it appears to be doing something similar to what you are asking about.

    I have added the code to the image to assist, note that Fred reports to himself and therefore the Manager field is empty
    Jerry

  6. #6
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need a little help in converting Oracle SQL (

    Thanks Hans & Jerry.

    Hans, we have a few Access DB's where we are connected to the Oracle tables through a ODBC connection. Some of this data is then joined to local Access tables or at times we import the Oracle table if small enough to work with locally as the ODBC connection sometimes has much to be desired (IE: A query that may take over a minute or 2 to run bombs). Other tables may have 100's of millions of records. I don't have very many SQL tools currently available to me where I can create & merge the data with the local Access data. Currently I am using Oracle SQL Developer when I can leave Access out of the process.

    In the Example I am running against a employee table where I am trying to get a list of all employees that roll up to a specific or list of manager ID's. Here is a little more detailed of an example:

    Emp......Mgr
    Joe
    Pam.....Joe
    Mary.....Pam
    Pat........Mary
    Fred.....Pat
    Scot.....Fred
    Larry...Scot
    Jim......Larry
    John...Larry

    So in my query if I enter Mary, I would want a list of Pat, Fred, Scot, Larry, Jim & John. Only Pat reports directly to Mary, the rest report to someone that reports to someone that reports to Mary.

    In one example I am tryting to work on I have a Access table with 1.5 million or so records that gets added to monthly from another job that does run against the Oracle tables, but may take 2 hours to run. I have a need to then produce some monthly / quarterly or yearly metrics against this Access data where the Employee heirarchy would come into play (IE: order count for a date range of all people that roll up to Fred)

    Jerry, your example does work for me except for limiting where the top tier starts. Here is my version of your example:

    SELECT DISTINCT a.HRID, a.EMAIL_HANDLE, a.EMP_LEVEL, a.SUPV_HRID, b.EMAIL_HANDLE AS Supv_Handle, b.EMP_LEVEL AS Supv_Level
    FROM Headcount AS a, Headcount AS b
    WHERE (((a.SUPV_HRID)=b.Hrid))
    ORDER BY a.EMAIL_HANDLE;

    So what I need to add is a filter for the SUPV_HRID to give me everyone that reports to that field, or to someone that reports to someone that reports to that id.
    For each employee, it does list who their supervisors HRID is (SUPV_HRID). This is why I am using - WHERE (((a.SUPV_HRID)=b.Hrid))


    Thanks to both of you for your suggestions & examples.
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need a little help in converting Oracle SQL (

    I understand what you want, but there is no simple way to get it. As I wrote, you either need a query with as many instances of the table as there are hierarchical levels, or you need a recursive function. Neither is as easy or convenient as START WITH ... CONNECT BY.

Posting Permissions

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