SQL in propagated data

Question

SQL in propagated data

Navigation

#1 by (1 votes)

0

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

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

In this structure, Matriz is related to at least Filial and it contains the link to Produto . But a Filial can have a Filial , which can have a Filial , etc.

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

I was successful testing the sub-select below with a separate sample record. But I can not extract all the records.

I tried to do this (hopefully not confusing), but subselect does not 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;

sql oracle

asked by anonymous 06.04.2016 / 16:34

1 answer

Temporarily disable WooCommerce store return fetch_object ()

score 1 · Answer 1

Have you tried this?

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;

How should the data structure be? I did not quite understand the relationship between the tables.