Need help to solve this scenario

ETL vs ELTL 26 Reputation points
2021-06-25T12:03:14.793+00:00

Hi All,

I need to write a recursive code but can't find the solution yet.

Scenario:

  1. There is a table (ERPCustomer) that has 2 columns ERPCustomerBK, SF_Account
  2. Sample Values
    ERPCustomer_BK, SF_AccountID
    AXPT5|75388|US-CA 0011400001a9G6IAAU
    AXPT5|74334|US-CA 001a000001BCKyXAAX

I need to find out the SF_AccountID for 1 ERPCustomer_BK.
"AXPT5|75388|US-CA" BK I have 2 SF_Account in my table "0011400001a9G6IAAU", "001a000001BCKyXAAX"

Now I need to check for these 2 SF_AccountID do we have any other ERPCustomer_BK in the data

This should go upto no further data found either for ERPCustomer_BK OR SF_AccountID.

109355-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-06-26T18:34:49.69+00:00

    As Ronen said, you went a little overboard that with the test data. For most questions of this type we rarely need more than 20 rows.

    Anyway, here is a query, which may do what you are looking for. This is a recursive CTE, and the extra column datastr serves to prune combinations we have already found.

    WITH rekurs AS (
       SELECT erpcustomer_bk, sf_accountID, 
              cast(erpcustomer_bk + ' ' + sf_accountID as nvarchar(MAX)) AS datastr
       FROM   temp.Erpx 
       WHERE  erpcustomer_bk = '5|75388|US-CA' 
       UNION ALL
       SELECT E.erpcustomer_bk, E.sf_accountID, 
              r.datastr + '///' + E.erpcustomer_bk + ' ' + E.sf_accountID
       FROM   rekurs r
       JOIN   temp.Erpx E ON E.erpcustomer_bk = r.erpcustomer_bk OR
                             E.sf_accountID   = r.sf_accountID
       WHERE  charindex(E.erpcustomer_bk + ' ' + E.sf_accountID, r.datastr) = 0
    )   
    SELECT erpcustomer_bk, sf_accountID
    FROM  rekurs
    

    I am not sure that this is the most efficient solution. Maybe it is better to run a loop where you store the partial results in a temp table. Then you can toggle on which column you are making lookups on. But I leave it to others to explore that area.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2021-06-25T19:14:07.707+00:00

    You appear to be saying you have a parent/child relationship.

    I suggest you start here:
    https://www.codeproject.com/Articles/818694/SQL-Queries-to-Manage-Hierarchical-or-Parent-child

    0 comments No comments

  2. ETL vs ELTL 26 Reputation points
    2021-06-26T17:45:13.533+00:00

    Please find the attached zipped file for DDL and DML script.
    Table name: ERPX

    you can try for ERPCustomer_BK ='5|75388|US-CA'

    109585-xx5.txt