START WITH CONNECTION and CONNECT in Oracle SQL

Question

I have a table below

ACCOUNT

CUSTOMER_ID PAYING_ACCOUNT_ID PARENT_ACCOUNT_ID ACCOUNT_ID COMPANY_ID 24669 24669 24669 24669 0 24671 24671 24669 24671 0 24670 24670 24669 24670 0 3385217 3385217 24670 3385217 0 158 158 158 158 0 159 159 158 159 0 160 160 159 160 0 161 161 160 161 0 162 162 160 162 0 180 180 180 180 0 

This is DDL

 CREATE TABLE "SYSTEM"."ACCOUNT" ("CUSTOMER_ID" NUMBER(20,0) NOT NULL ENABLE, "PAYING_ACCOUNT_ID" NUMBER(20,0), "PARENT_ACCOUNT_ID" NUMBER(20,0), "ACCOUNT_ID" NUMBER, "COMPANY_ID" NUMBER) 

This is my request.

  select lpad(' ', 2*level) || A.ACCOUNT_ID AS LEVEL_LABEL, LEVEL, A.* from ACCOUNT A start with PARENT_ACCOUNT_ID IN (select PARENT_ACCOUNT_ID from ACCOUNT where ACCOUNT_ID IN (select PARENT_ACCOUNT_ID from ACCOUNT where parent_account_id != account_id) and ACCOUNT_ID = PARENT_ACCOUNT_ID) CONNECT BY NOCYCLE PRIOR A.ACCOUNT_ID = A.PARENT_ACCOUNT_ID; 

The main task of the query is to select data that is hierarchical, which are PARENT_ACCOUNT_ID and ACCOUNT_ID, however I received duplicate data returned by the query

Any advice is greatly appreciated. Thanks

Answer 1

Why not just:

  SELECT level, * FROM accounts START WITH parent_account_id = account_id CONNECT BY PRIOR account_id = parent_account_id AND account_id <> parent_account_id 

?

Answer 2

You can use the PL / SQL HOST command to call .exe files.