Find document that originated the following

Question

I have a table where a document can originate new documents, and this new document loads in a column the number of the originating document (not necessarily the first document that ever existed).

There may be several documents after the originator, but I always need to find the first document. In the example document 112 generated 117, which generated 119, which generated 120, which generated 121, but the originator of everything, is 112.

I attached images to try to make more clear what I want to do, also script to create the table and an attempt to achieve the goal.

Table:

Desired:

Link files

I’d appreciate it if you could help.

The ideal would be independent of how many documents there are I manage to reach the originator, but if I have to do for a fixed amount of documents, I believe it also serves the purpose. Even about the title of the question, I could not think of something better. I accept criticism to be able to make more coherent with the need.

attempt:

 SELECT cliente,num_doc,num_doc_anterior,
 (SELECT( IIF((num_doc_anterior =''),(num_doc),
 (IIF((num_doc_anterior<>''),
 (select num_doc from DADOS where num_doc =dd.num_doc_anterior),
 'XXX'))))) AS doc_original
 FROM DADOS dd

Answer 1

Assuming that each customer has only one doc_original you could do it like this:

WITH    q AS 
        (
        SELECT  *
        FROM    dados
        WHERE   num_doc_anterior IS NULL -- this condition defines the ultimate ancestors in your chain, change it as appropriate
        UNION ALL
        SELECT  m.*
        FROM    dados m
        JOIN    q
        ON      m.num_doc = q.num_doc_anterior
        )
SELECT  d.*, q.num_doc as doc_original
FROM    q inner join dados d on
q.cliente = d.cliente

link to the sqlfiddle with the example