Tenho uma tabela de cadastro de produtos (cerca de 5.000 itens) chamada TAB_PRODUTO, e nessa tabela tenho as seguintes colunas: COD_PRODUTO (código do produto) e TIPO_PRODUTO (C: comprado, P: produzido). A query precisa basicamente trazer essa tabela e inserir uma coluna adicional denominada CUSTO_PRODUTO, com o custo unitário de cada produto, calculado conforme segue:
Quando o produto é tipo C (comprado), o custo dele será buscado em uma tabela que possui o custo de todos os itens comprados, denominada TAB_CUSTO. Essa parte é tranquilo, essa tabela possui as seguintes colunas: COD_PRODUTO (código do produto) e VALOR_CUSTO_COMPRA.
Quando o item é tipo P (produzido), seu custo será a soma do custo dos produtos filhos, calculados a partir de uma tabela BOM (Bill of Materials) chamada TAB_BOM, e é nessa parte que enrosquei. Essa tabela possui as seguintes colunas: COD_PRODUTO_PAI, COD_PRODUTO_FILHO e QTD_NECESSARIA.
Se um produto da TAB_PRODUTO é um item produzido e é formado, por exemplo, por 10 componentes diferentes, na TAB_BOM haverá 10 registros referentes a esse item. Os 10 registros terão o mesmo COD_PRODUTO na coluna COD_PRODUTO_PAI, e cada qual terá um COD_PRODUTO_FILHO e QTD_NECESSARIA diferentes.
O custo de um produto produzido da TAB_PRODUTO então será a soma do custo de todos seus filhos. Quando um filho é um item comprado, basta eu multiplicar a QTD_NECESSARIA da TAB_BOM pelo VALOR_CUSTO_COMPRA da tabela TAB_CUSTO. Quando um filho é produzido, eu teria que multiplicar a quantidade dele pela soma do custo dos seus próprios filhos, ou seja, netos do primeiro produto em questão.
Nos exemplos que encontrei no fórum, vi o pessoal usando connect, porém a BOM dos exemplos geralmente possuía o nível hierárquico dos itens em questão, mas na minha tabela BOM não há indicação do nível, creio que pelo fato de um item ser usado como componente de diversos produtos.
Estava desenvolvendo algo nessa linha de raciocínio, mas creio estar errado:
SELECT T1.COD_PRODUTO,
CASE WHEN T1.TIPO_ITEM = 'C'
THEN T2.VALOR_CUSTO_COMPRA
ELSE SELECT SUM(CASE WHEN(T3.COD_PRODUTO_PAI = T1.COD_PRODUTO AND T1.TIPO_ITEM = 'C')
THEN T3.QTD_NECESSARIA * T4.VALOR_CUSTO_COMPRA
ELSE T3.QTD_NECESSARIA * SELECT...??? (LOOPING PARA CADA VEZ QUE ELE ENCONTRA UM FILHO TIPO 'P')
FROM TAB_BOM T3
INNER JOIN TAB_CUSTO T4 ON (T4.COD_PRODUTO = T3.COD_PRODUTO_FILHO)
FROM TAB_PRODUTO T1
INNER JOIN TAB_CUSTO T2 ON (T2.COD_PRODUTO = T1.COD_PRODUTO)
Se alguém puder ajudar, ficarei muito grato.