Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2013
    Posts
    3

    Unanswered: Hierarchical Query with Joins

    Hi,

    I am trying to write rather complex hierarchical query. I am fairly new to DB" and Hierarchical queries. Also, the DB seems to have evolved, rather than been created.

    I seem to be unaware of the limitations of 'CONNECT BY' because I am getting this error:


    My Goal:

    LOAN_ACCOUNT.ACCOUNT_ID is comprised of '00'+NSC+ACCOUNTNUMBER of the parent account of its related ACCOUNT.ACCOUNT_ID

    I want this query to return the lineage of a given NSC and ACCOUNT_NUMBER.


    Here's sample Data

    ACCOUNT
    ACCOUNT_ID |NSC |ACCOUNT_NUMBER |
    1 |qwerty |12345678
    2 |asdfgh |78945612
    3 |bnmxcv |45678912
    4 |dfghjk |14785236


    APPLICATION_ACCOUNT
    |LOAN_PART_ID |ACCOUNT_ID
    1 |1 |
    2 |2 |
    3 |3 |
    4 |4 |


    LOAN_PART
    | LOAN_DETAIL_ID |LOAN_PART_ID |
    | 1 | 1 |
    | 2 | 2 |
    | 3 | 3 |
    | 4 | 4 |


    LOAN_ACCOUNT
    | ACCOUNT_ID |LOAN_DETAIL_ID
    | 00asdfgh78945612 |1
    | 00bnmxcv45678912 |2
    | 00dfghjk14785236 |3
    | |4



    The combination 'dfghjk' and '14785236' will return nothing (no parent):

    'qwerty' and '12345678' should return 3 rows:

    00asdfgh78945612
    00bnmxcv45678912
    00dfghjk14785236

    Thanks,
    Last edited by hellishheat; 11-25-13 at 04:52.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please post more useful sample data.

    (1) I can't see necessity of tables APPLICATION_ACCOUNT and LOAN_PART in your sample data.
    Because,
    (1-1) LOAN_PART_ID and ACCOUNT_ID in APPLICATION_ACCOUNT table were same.
    (1-2) LOAN_DETAIL_ID and LOAN_PART_ID in LOAN_PART table were same.
    So, it is possible to join ACCOUNT table and LOAN_ACCOUNT table directly
    by LOAN_ACCOUNT.LOAN_DETAIL_ID = ACCOUNT.ACCOUNT_ID

    (2) It is better to post DDLs(e.g. CREATE TABLE) and INSERT statements to show sample data.
    Because,
    (2-1) other persons can test their ideas easily,
    (2-2) and make data type and values clear.
    Some unclear data type and values in your sample were...
    (2-2-1) data type of account_number.
    INTEGER, CHAR, or VARCHAR?
    (2-2-2) the value of account_id in loan_account of which loan_detail_id = 4
    NULL or string of length=0?

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    As an aside, the CONNECT BY syntax is only available in DB2 databases created in the Oracle compatibility mode. I suggest you use the standard way of writing recursive queries, using recursive CTEs.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by n_i View Post
    .... I suggest you use the standard way of writing recursive queries, using recursive CTEs.
    I really agree with that.


    If ACCOUNT could be joined with LOAN_ACCOUNT directly (like the assumption poined out in (1) in my previous post),
    I thought that Example 1 might be useful for you.

    Example 1: Assumed the answer to (2-2-2) was NULL.
    Code:
    WITH
     joined_table AS (
    SELECT ac.nsc
         , ac.account_number
         , la.account_id
     FROM  account      AS ac
     INNER JOIN
           loan_account AS la
      ON   la.loan_detail_id = ac.account_id
    )
    , recursive_cte
    ( k , account_id ) AS (
    SELECT 1
         , account_id
     FROM  joined_table
     WHERE nsc            = 'qwerty'
       AND account_number = 12345678
    UNION ALL
    SELECT k + 1
         , jt.account_id
     FROM  recursive_cte AS pre/*vious*/
         , joined_table  AS jt
     WHERE pre.k < 100
       AND jt.nsc            = SUBSTR(pre.account_id , 3 , 6)
       AND jt.account_number = SUBSTR(pre.account_id , 9 , 8)
    )
    SELECT account_id
     FROM  recursive_cte
     WHERE account_id IS NOT NULL
    ;

  5. #5
    Join Date
    Nov 2013
    Posts
    3
    Quote Originally Posted by tonkuma View Post
    I really agree with that.


    If ACCOUNT could be joined with LOAN_ACCOUNT directly (like the assumption poined out in (1) in my previous post),
    I thought that Example 1 might be useful for you.

    Example 1: Assumed the answer to (2-2-2) was NULL.
    Code:
    WITH
     joined_table AS (
    SELECT ac.nsc
         , ac.account_number
         , la.account_id
     FROM  account      AS ac
     INNER JOIN
           loan_account AS la
      ON   la.loan_detail_id = ac.account_id
    )
    , recursive_cte
    ( k , account_id ) AS (
    SELECT 1
         , account_id
     FROM  joined_table
     WHERE nsc            = 'qwerty'
       AND account_number = 12345678
    UNION ALL
    SELECT k + 1
         , jt.account_id
     FROM  recursive_cte AS pre/*vious*/
         , joined_table  AS jt
     WHERE pre.k < 100
       AND jt.nsc            = SUBSTR(pre.account_id , 3 , 6)
       AND jt.account_number = SUBSTR(pre.account_id , 9 , 8)
    )
    SELECT account_id
     FROM  recursive_cte
     WHERE account_id IS NOT NULL
    ;
    thanks tonkuma and n_i,

    tonkuna, those tables are required, I simplified the data to make my problem easier to understand.

    I wasn't aware that CONNECT BY may not have been available to me. hmmm.

    I will take tonkuma's solution and try to adapt it to use the other two tables.

    Thanks again.

  6. #6
    Join Date
    Nov 2013
    Posts
    3
    Great stuff. I do need those interim tables (I had just simplified the data to make the problem more easily understood).

    I've adapted your SQL below and it works perfectly. Thanks.

    Quote Originally Posted by tonkuma View Post
    I really agree with that.


    If ACCOUNT could be joined with LOAN_ACCOUNT directly (like the assumption poined out in (1) in my previous post),
    I thought that Example 1 might be useful for you.

    Example 1: Assumed the answer to (2-2-2) was NULL.
    Code:
    WITH
     joined_table AS (
    SELECT ac.nsc
         , ac.account_number
         , la.account_id
     FROM  account      AS ac
     INNER JOIN
           loan_account AS la
      ON   la.loan_detail_id = ac.account_id
    )
    , recursive_cte
    ( k , account_id ) AS (
    SELECT 1
         , account_id
     FROM  joined_table
     WHERE nsc            = 'qwerty'
       AND account_number = 12345678
    UNION ALL
    SELECT k + 1
         , jt.account_id
     FROM  recursive_cte AS pre/*vious*/
         , joined_table  AS jt
     WHERE pre.k < 100
       AND jt.nsc            = SUBSTR(pre.account_id , 3 , 6)
       AND jt.account_number = SUBSTR(pre.account_id , 9 , 8)
    )
    SELECT account_id
     FROM  recursive_cte
     WHERE account_id IS NOT NULL
    ;

Tags for this Thread

Posting Permissions

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