SQL in propagated data

Question

I need to hit the SQL below for the database Oracle.

The idea is to retrieve the code from the table Produto associated with the table code Matriz.

In this structure, the Matriz is related to at least one Filial and this contains the link to Produto. But a Filial may have a Filial, that may have a Filial, etc..

That is, to access the related codes in the tables Produto and Matriz, I need a backward propagation of existing information in the relational table Filial, which I resolved with the START WITH and CONNECT BY PRIOR.

I succeeded by testing the sub-select below separately with an example record. But I can’t extract all the records.

I tried to do it this way (I hope you’re not confused), but subselect doesn’t see the external table.

select produto.nome, produto.qtde, tabelaAux.codigo from produto
  left join (select * from
    (select matriz.codigo as codMatriz, matriz.codFilial as codFilial
    from matriz
    left join filial on filial.codC = matriz.codigo
    start with filial.codA = produto.codigo          --falha aqui
    connect by prior filial.codigoPai = filial.codigo)
  where codMatriz is not null)
  tabelaAUX on tabelaAUX.codFilial = produto.codFilial;

Answer 1

Have you tried that?

SELECT PRODUTO.NOME,
PRODUTO.QTDE,
TABELAAUX.CODIGO,
TABELAAUX.NOME_MATRIZ,
TABELAAUX.NOME_FILIAL
FROM PRODUTO
LEFT JOIN
(
    SELECT MATRIZ.CODIGO AS CODMATRIZ,
      MATRIZ.NOME AS NOME_MATRIZ,
      FILIAL.NOME AS NOME_FILIAL,
      MATRIZ.CODFILIAL    AS CODFILIAL,
      FILIAL.CODA,FILIAL.CODIGO,FILIAL.CODIGOPAI 
    FROM MATRIZ
    LEFT JOIN FILIAL   ON FILIAL.CODC= MATRIZ.CODIGO
    WHERE MATRIZ.CODIGO                   IS NOT NULL
) TABELAAUX ON TABELAAUX.CODFILIAL = PRODUTO.CODFILIAL
START WITH TABELAAUX.CODA            = PRODUTO.CODIGO 
CONNECT BY NOCYCLE PRIOR TABELAAUX.CODIGOPAI = TABELAAUX.CODIGO; 

What should the data structure look like? I did not understand the relationship between the tables.