Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Unanswered: a query regaarding SQL Query

    Hi All,
    I have a scenario where I am retrieving function call graph using START WITH .. CONNECT BY clause from a table.
    A snapshot of output is given below:
    SELECT LEVEL, SYS_CONNECT_BY_PATH(CALLER_FUNC,'/') FROM IAT_MODULARIZATION
    START WITH CALLED_FUNC = 'processAmtSlabDtls'
    CONNECT BY nocycle PRIOR CALLER_FUNC = CALLED_FUNC
    /
    LEVEL SYS_CONNECT_BY_PATH(CALLER_FUNC '/')
    1 /addAmtSlab
    2 /addAmtSlab/SRV_AddAmtSlab
    3 /addAmtSlab/SRV_AddAmtSlab/NAPI_AddAmtSlab
    3 /addAmtSlab/SRV_AddAmtSlab/processAmtSlabRecords
    4 /addAmtSlab/SRV_AddAmtSlab/processAmtSlabRecords/main_rebx00021 /cancelAmtSlab
    2 /cancelAmtSlab/SRV_CancelAmtSlab
    3 /cancelAmtSlab/SRV_CancelAmtSlab/NAPI_CancelAmtSlab
    1 /deleteAmtSlab
    2 /deleteAmtSlab/SRV_DeleteAmtSlab
    3 /deleteAmtSlab/SRV_DeleteAmtSlab/NAPI_DeleteAmtSlab
    1 /modifyAmtSlab
    2 /modifyAmtSlab/SRV_ModifyAmtSlab

    3 /modifyAmtSlab/SRV_ModifyAmtSlab/NAPI_ModifyAmtSlab

    3 /modifyAmtSlab/SRV_ModifyAmtSlab/processAmtSlabRecords
    4 /modifyAmtSlab/SRV_ModifyAmtSlab/processAmtSlabRecords/main_rebx0002
    1 /verifyAmtSlab
    2 /verifyAmtSlab/SRV_VerifyAmtSlab
    3 /verifyAmtSlab/SRV_VerifyAmtSlab/NAPI_VerifyAmtSlab

    Here I want a single query, which gives me the highlighted part as an output.
    That is, I want different and compete execution path for the given function. So any sub part should not appear in the output.
    Is there any way to achieve this?

    Any suggestion will be highly appreciated.

    Please let me know in case any more details are required.

    Thanks in advance.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Yes, check whether the value of CONNECT_BY_ISLEAF is 1 in WHERE condition.

    This pseudocolumn is described in SQL Reference book, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/
    Please, have a look into the one for your Oracle version for more details.

Posting Permissions

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