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

MT0 · Accepted Answer · 2015-11-09 14:11:52Z

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 9, 2015 at 14:11

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

andreww Over a year ago

OMG! I would never guess how to do that< I need to analyze it : ). It works perfect ;) Thanks!

MT0 Over a year ago

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.

andreww Over a year ago

Right, now i get it :) Thanks again! :)

Collectives™ on Stack Overflow

hierarchy query (connect by clause)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related