hierarchy query (connect by clause)

Question

I'm trying to writte a query using connect by clause but I can't handle with that.

Generally I have table:

CREATE TABLE "TESTOWA" (
    "ACCOUNT" VARCHAR2(20 BYTE), 
    "PARENT"  VARCHAR2(20 BYTE), 
    "PAYMENT" VARCHAR2(20 BYTE)
);

Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('5436','5436','1');
Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('4576','3457',null);
Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('5763','5686','1');
Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('5686','5686',null);
Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('3457','5686',null);

And now, what I want to do is find an accounts which payment column is not filled (even parent account) Every account can have parent account (parent column) which indicate to other account id. Maybe it will be easier if I present it on example:

 ACCOUNTID | PARENT | PAYMENT
-----------------------------
    5436   |  5436  |    1
    4576   |  3457  |  NULL
    5763   |  5643  |    1
    5686   |  5686  |    1
    3457   |  5686  |  NULL

First account is okay - payment column is filled. Second is not okay because is null - but as we can see there is a parent account so now we check (3457 account) , and again payment column is null but again there is a parent account (5686) and finally there is a payment column filled. So for situatios above select should not present nothing What if Table would looks:

 ACCOUNTID | PARENT | PAYMENT
------------------------------
    5436   |  5436  |    1
    4576   |  3457  |  NULL
    5763   |  5643  |    1
    5686   |  5686  |  NULL
    3457   |  5686  |  NULL

As we can see the only one change is null next to 5686 account id, so correct select should present accounts: 4576, 3457, 5686

score 1 · Accepted Answer · answered Nov 09 '15 at 14:11

1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE "TESTOWA" (   
  ACCOUNT NUMBER(4,0), 
  PARENT  NUMBER(4,0), 
  PAYMENT NUMBER(1,0)
);
Insert into TESTOWA values (5436,5436,1);
Insert into TESTOWA values (5686,5686,null);
Insert into TESTOWA values (5763,5686,1);
Insert into TESTOWA values (3457,5686,1); 
Insert into TESTOWA values (4576,3457,null);

Query 1:

SELECT t.*,
       CONNECT_BY_ROOT( PAYMENT ) AS HAS_PAYED
FROM   TESTOWA t
START WITH
       ACCOUNT = PARENT
OR     PAYMENT = 1
CONNECT BY
       NOCYCLE
       PRIOR ACCOUNT = PARENT
AND    PAYMENT IS NULL

Results:

| ACCOUNT | PARENT | PAYMENT | HAS_PAYED |
|---------|--------|---------|-----------|
|    5436 |   5436 |       1 |         1 |
|    3457 |   5686 |       1 |         1 |
|    4576 |   3457 |  (null) |         1 |
|    5686 |   5686 |  (null) |    (null) |
|    5763 |   5686 |       1 |         1 |

answered Nov 09 '15 at 14:11

MT0

143,790
11
59
117

OMG! I would never guess how to do that< I need to analyze it : ). It works perfect ;) Thanks! – andreww Nov 09 '15 at 14:14
`START WITH ... PAYMENT = 1` ensures that all paid accounts are at the root of the hierarch and `CONNECT BY ... PAYMENT IS NULL` ensures that payed accounts are not included in other hierarchies (so you don't have to worry about double counting). The other bits of the `START WITH` and `CONNECT BY` just include the other (unpaid) accounts into the select. Since each paid account is always at the root of the hierarchy then `CONNECT_BY_ROOT( PAYMENT )` will tell you whether you've paid or not. – MT0 Nov 09 '15 at 14:21
Right, now i get it :) Thanks again! :) – andreww Nov 09 '15 at 14:31

hierarchy query (connect by clause)

1 Answers1

Related