Need help to solve this scenario

Question

Need help to solve this scenario

ETL vs ELTL 26

Hi All,

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

Scenario:

There is a table (ERPCustomer) that has 2 columns ERPCustomerBK, SF_Account
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.

Olaf Helper 27,051 Reputation points

2021-06-25T12:22:23.383+00:00

Screenshots of T-SQL statement are useless.
Please post table design as DDL, some sample data as DML statement and the expected result
Erland Sommarskog 72,236 Reputation points MVP

2021-06-25T15:53:33.27+00:00

In case you are unfamiliar with the abbreviations that Olaf uses, here are some clarification:

DDL = Data Definition Language. That is, CREATE TABLE statement for your table(s).

DML = Data Manipulation Language. That is, INSERT statements with sample data.

This permits to copy and paste into a query window to develop a tested solution. Well, we also need to know the expected result given the test data.

Without this we can only supply guesses or untested solutions, which often prove to be frustrating for everyone. Or we can simply do something else. After all, most of us here answer questions in our free time.
ETL vs ELTL 26 Reputation points

2021-06-26T17:45:55.92+00:00

Please find the DDL and DML command in below attached file.
ETL vs ELTL 26 Reputation points

2021-06-26T17:47:01.577+00:00

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

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

109548-xx5.txt

Olaf Helper 27,051 Reputation points

2021-06-25T12:22:23.383+00:00

Screenshots of T-SQL statement are useless.
Please post table design as DDL, some sample data as DML statement and the expected result
Erland Sommarskog 72,236 Reputation points MVP

2021-06-25T15:53:33.27+00:00

In case you are unfamiliar with the abbreviations that Olaf uses, here are some clarification:

DDL = Data Definition Language. That is, CREATE TABLE statement for your table(s).

DML = Data Manipulation Language. That is, INSERT statements with sample data.

This permits to copy and paste into a query window to develop a tested solution. Well, we also need to know the expected result given the test data.

Without this we can only supply guesses or untested solutions, which often prove to be frustrating for everyone. Or we can simply do something else. After all, most of us here answer questions in our free time.
ETL vs ELTL 26 Reputation points

2021-06-26T17:45:55.92+00:00

Please find the DDL and DML command in below attached file.
ETL vs ELTL 26 Reputation points

2021-06-26T17:47:01.577+00:00

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

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

109548-xx5.txt

Answer 1

Erland Sommarskog 72,236 MVP

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.

Ronen Ariely 14,131 Reputation points MVP

2021-06-27T10:30:52.013+00:00

OK, only now that I see Erland's solution I finally understand the question's request :-)

CTE looks good solution in first glance. Only need to confirm there is index on erpcustomer_bk, sf_accountID for the comparing in the loop (it will reduce the read)
Erland Sommarskog 72,236 Reputation points MVP

2021-06-27T10:35:47.767+00:00

OK, only now that I see Erland's solution I finally understand the question's request :-)

At least you understand my interpretation of it! I've been wrong before.

Only need to confirm there is index on erpcustomer_bk, sf_accountID for the comparing in the loop (it will reduce the read

I think we need an index on each column, and given how poor SQL Server is at handling OR conditions, I think a loop where we can control which column we are looking by may be more efficient.
ETL vs ELTL 26 Reputation points

2021-06-27T11:41:58.077+00:00

Thank you Erland for your query.. My nightmares are over now,.. I can sleep well today.

Answer 2

Tom Phillips 17,611

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

Answer 3

ETL vs ELTL 26

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

Ronen Ariely 14,131 Reputation points MVP

2021-06-26T18:17:33.827+00:00

Thanks for providing the DDL+DML

With that being said, we only need a sample data so we will be able to discuss the requested result set and you provided more than 5k rows.

Can you provide a smaller set of rows and explain what is your requested result set according to the sample data?

Need help to solve this scenario

2 additional answers

Activity