Query Hierarquica - Registros Pais sem filhos

[Gustavo S. Hahn 0]

Bom dia!

Tenho a seguinte situação.... Preciso que uma consulta retorne apenas os registros PAIS que possuam filhos.... Os registros pais sem filhos, não devem aparecer na consulta.

No exemplo abaixo, deve aparecer somente do número 000001 até o 000006, pois os codigos  45454545 e 58789121 não pertencem a Ordem 07896. E o Pai 000010 não tem filhos mesmo.

Alguém tem alguma ideia?

 

Dados:

 

TABELA 1
Numero	Num Pai		Tipo	Produto
000001			Pai
000002	000001		Filho	02233556
000003	000001		Filho	02243551
000004			Pai
000005	000004		Filho	33225566
000006	000004		Filho	77889999
000007			Pai
000008	000007		Filho	45454545
000009	000007		Filho	58789121
000010			Pai
TABELA2     Ordem Item Produto 07896 1 02233556 07896 2 33225566 07896 3 77889999 07896 4 02243551

Consulta:

SELECT NUMERO,NUMPAI,NOME,TIPO
FROM TABELA TB1
  START WITH TB1.NUMERO >= '000001'
  CONNECT BY TB1.NUMPAI = PRIOR TB1.NUMERO
AND TB1.PRODUTO IN
  (SELECT TB2.PRODUTO
  FROM TABELA2 TB2
  WHERE TB2.ORDEM IN('07896')
  )
ORDER SIBLINGS BY TB1.NUMERO,
  TB1.PRODUTO

 

Executando a consulta acima, retorna assim... Os últimos 2 Pais não devem aparecer.

 

Numero	Num Pai	Nome	Tipo	Produto
000001			Pai
000002	000001		Filho	02233556
000003	000001		Filho	02243551
000004			Pai
000005	000004		Filho	33225566
000006	000004		Filho	77889999
000007			Pai
000010			Pai

[Motta 645]

Gere uma view com este sql , faça um not exists com esta view

[Gustavo S. Hahn 0]

Opa!

Vai funcionar.... Mas preciso disso na mesma consulta. Será que é possível?

[Motta 645]

Creio que sim , pode ficar lento se a tabela for muito grande 

[Gustavo S. Hahn 0]

1 hora atrás, Motta disse:

Creio que sim , pode ficar lento se a tabela for muito grande 

 

Tem ideia de como fazer? Porque já tentei de n formas e não tem jeito....

[Motta 645]

CREATE OR REPLACE VIEW V_PAIS_FILHOS AS
SELECT NUMERO,NUMPAI,NOME,TIPO
FROM TABELA TB1
  START WITH TB1.NUMERO >= '000001'
  CONNECT BY TB1.NUMPAI = PRIOR TB1.NUMERO
AND TB1.PRODUTO IN
  (SELECT TB2.PRODUTO
  FROM TABELA2 TB2
  WHERE TB2.ORDEM IN('07896')
  )

 

 

SELECT *
FROM V_PAIS_FILHOS V1
WHERE EXISTS (SELECT NULL
              FROM V_PAIS_FILHOS V1
              WHERE V2.NUMPAI = V1.NUM)

 

[Gustavo S. Hahn 0]

 

1 hora atrás, Motta disse:

SELECT *
FROM V_PAIS_FILHOS V1
WHERE EXISTS (SELECT NULL
              FROM V_PAIS_FILHOS V1
              WHERE V2.NUMPAI = V1.NUM)

 

 

Não seria exatamente isso...

Ajustando...

SELECT *
FROM V_PAIS_FILHOS V1
WHERE EXISTS (SELECT NULL
              FROM V_PAIS_FILHOS V2
              WHERE V2.NUMPAI =  V1.NUMERO);

Retorna apenas 3 registros...

 

NUMERO           TIPO

000001               Pai
000004               Pai
000007               Pai

 

Veja...

CREATE TABLE TABELA1 (
    "NUMERO_SEQ" CHAR(6 BYTE) DEFAULT '      ' NOT NULL ENABLE, 
    "NUMERO_PAI" CHAR(6 BYTE) DEFAULT '      ' NOT NULL ENABLE, 
    "TIPO" CHAR(6 BYTE) DEFAULT '      ' NOT NULL ENABLE,
    "PRODUTO" CHAR(8 BYTE) DEFAULT '        ' NOT NULL ENABLE, 
    "ID" NUMBER DEFAULT 0.0 NOT NULL ENABLE, 
     CONSTRAINT "TABELA1_PK" PRIMARY KEY ("ID"));
    
CREATE TABLE TABELA2 (
    "NUMORDEM" CHAR(5 BYTE) DEFAULT '     ' NOT NULL ENABLE, 
    "ITEM" CHAR(3 BYTE) DEFAULT '   ' NOT NULL ENABLE, 
    "PRODUTO" CHAR(8 BYTE) DEFAULT '        ' NOT NULL ENABLE, 
    "ID" NUMBER DEFAULT 0.0 NOT NULL ENABLE, 
     CONSTRAINT "TABELA2_PK" PRIMARY KEY ("ID"));
     
INSERT INTO TABELA1 (NUMERO_SEQ,NUMERO_PAI,TIPO,PRODUTO,ID) values ('000001',' ','Pai',' ',1);
INSERT INTO TABELA1 (NUMERO_SEQ,NUMERO_PAI,TIPO,PRODUTO,ID) values ('000002','000001','Filho','02222222',2);
INSERT INTO TABELA1 (NUMERO_SEQ,NUMERO_PAI,TIPO,PRODUTO,ID) values ('000003','000001','Filho','03333333',3);
INSERT INTO TABELA1 (NUMERO_SEQ,NUMERO_PAI,TIPO,PRODUTO,ID) values ('000004',' ','Pai',' ',4);
INSERT INTO TABELA1 (NUMERO_SEQ,NUMERO_PAI,TIPO,PRODUTO,ID) values ('000005','000004','Filho','04444444',5);
INSERT INTO TABELA1 (NUMERO_SEQ,NUMERO_PAI,TIPO,PRODUTO,ID) values ('000006','000004','Filho','05555555',6);
INSERT INTO TABELA1 (NUMERO_SEQ,NUMERO_PAI,TIPO,PRODUTO,ID) values ('000007',' ','Pai',' ',7);
INSERT INTO TABELA1 (NUMERO_SEQ,NUMERO_PAI,TIPO,PRODUTO,ID) values ('000008','000007','Filho','06666666',8);
INSERT INTO TABELA1 (NUMERO_SEQ,NUMERO_PAI,TIPO,PRODUTO,ID) values ('000009','000007','Filho','07777777',9);
INSERT INTO TABELA1 (NUMERO_SEQ,NUMERO_PAI,TIPO,PRODUTO,ID) values ('000010',' ','Pai',' ',10);

INSERT INTO TABELA2 (NUMORDEM,ITEM,PRODUTO,ID) values ('07896','001','02222222',1);
INSERT INTO TABELA2 (NUMORDEM,ITEM,PRODUTO,ID) values ('07896','002','03333333',2);
INSERT INTO TABELA2 (NUMORDEM,ITEM,PRODUTO,ID) values ('07896','003','04444444',3);
INSERT INTO TABELA2 (NUMORDEM,ITEM,PRODUTO,ID) values ('07896','004','05555555',4);

 

SELECT NUMERO_SEQ,NUMERO_PAI,TIPO,PRODUTO
FROM TABELA1 TB1
START WITH TB1.NUMERO_SEQ >= '000001'
CONNECT BY PRIOR TB1.NUMERO_PAI =  TB1.NUMERO_SEQ
AND TB1.PRODUTO IN
(SELECT TB2.PRODUTO
FROM TABELA2 TB2
WHERE TB2.NUMORDEM IN('07896')
)
ORDER SIBLINGS BY TB1.NUMERO_SEQ,
TB1.PRODUTO;

 

O retorno é esse:

NUMERO_SEQ            NUMERO_PAI     TIPO       PRODUTO

000001                                                           Pai
000002                         000001                  Filho        02222222
000003                         000001                  Filho        03333333
000004                                                           Pai
000005                         000004                  Filho        04444444
000006                         000004                  Filho        05555555
000007                                                           Pai
000008                         000007                  Filho        06666666
000009                         000007                  Filho        07777777
000010                                                           Pai

 

 

Porém o que preciso é isso...

NUMERO_SEQ            NUMERO_PAI     TIPO       PRODUTO

000001                                                           Pai
000002                         000001                  Filho        02222222
000003                         000001                  Filho        03333333
000004                                                           Pai
000005                         000004                  Filho        04444444
000006                         000004                  Filho        05555555

 

Pois os produtos 06666666 e 07777777 não estão contidos na TABELA2.

Dessa forma, nem os Pais sem filhos devem aparecer (Numeros 000007 e 000010).

 

[Motta 645]

SELECT *
FROM V_PAIS_FILHOS V1
WHERE ( V2.NUMPAI is null or
        EXISTS (SELECT NULL
              FROM V_PAIS_FILHOS V2
              WHERE V2.NUMPAI =  V1.NUMERO) )