Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Location
    Brisbane, Australia
    Posts
    10

    Question Unanswered: START WITH parameter for CONNECT BY

    Hi all,

    I am a relative newbie to Oracle development.

    I am developing a report in Crystal Reports 8 with an Oracle 8i back-end.

    The tables involved are pretty straight forward:
    tbl_business_unit (PK: bu_id)
    tbl_employee (PK: emp_id; FK: bu_id)

    I want to use a CONNECT BY hierarchical query that displays results for all employees in a particular branch of the business unit hierarchy. This is pretty straight forward if I hardcode the bu_id, or even the emp_id:

    For example:

    SELECT bu_id, name, parent_id
    FROM tbl_business_unit
    START WITH bu_id = 'bu0023'
    CONNECT BY PRIOR bu_id = parent_id;

    SELECT bu_id, name, parent_id
    FROM tbl_business_unit
    START WITH bu_id = (SELECT bu_id FROM tbl_employee WHERE emp_id = 'emp0061')
    CONNECT BY PRIOR bu_id = parent_id;

    The problem with the above examples is that the ID parameters are hardcoded, and all the examples I have seen use this approach, but I need to pass the START WITH expression as a parameter at runtime - when this report is finally implemented the START WITH expression will be the business unit of the person running the report.

    The other problem is that I actually want to return data from tbl_employee (name, address, etc), but the above queries only return business unit data. Everything I have read says that I can't use joins with a CONNECT BY.

    Any help greatly appreciated!

    Regards,

    Paul Hobbs

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't know Crystal Reports, but the process for using a parameter for the START WITH value should be the same as the process for using a parameter in the WHERE clause, e.g. something like:

    START WITH :param1

Posting Permissions

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