ORACLE SQL Query with Parent and Child structure

Question

I have 2 tables which are Account tbl and Customer tbl, the structure is like below:

Account tbl

Customer_ID    Account_ID    Parent_Account_ID    
3780952        3780952       3780952
3780997        3780997       3780997
3781004        3781004       3780997

Customer tbl (Customer_Group have different value, but im only interest on Personal)

Customer_ID      Customer_Group
3781004          Personal
3780997          Personal
3780952          Personal

The rule to determine PS/NonPS, Principle, Supp as per below:

**PS/NonPs**
Customer_ID equal to Parent_Account and Parent_Account is unique (not exist more than 1)  then NonPs.
Customer_ID equal to Parent_Account and Parent_Account is non unique OR -   Customer_ID is not equal to Parent_Account then PS    
**Principle** 
IF NonPS then Principle is Null
IF PS - If Customer_ID equal to Parent_Account then Principle is Y else N
**Supp**
IF NonPS then Supp is Null
IF PS - If Customer_ID not equal to Parent_Account then supp is Y else N

The final output should be like this

Customer_ID    Account_ID    Parent_Account_ID   PS/NonPS   Principle   Supp
3780952        3780952       3780952             NonPS       Null        Null
3780997        3780997       3780997             PS          Y           N
3781004        3781004       3780997             PS          N           Y

I alredy tried many times but still cant get the output..anyone can help ?

Vincent Malgrat · Accepted Answer · 2012-01-17 11:11:47Z

With CASE and analytic functions:

SQL> WITH myData AS (
  2    SELECT 3780952 Customer_ID, 3780952 Account_ID, 3780952 Parent_Account_ID
  3      FROM DUAL
  4    UNION ALL SELECT 3780997, 3780997, 3780997 FROM DUAL
  5    UNION ALL SELECT 3781004, 3781004, 3780997 FROM DUAL
  6  )
  7  SELECT v.*,
  8         CASE WHEN ps = 'PS' AND customer_id = parent_account_id  THEN 'Y'
  9              WHEN ps = 'PS' THEN 'N'
 10         END "Principle",
 11         CASE WHEN ps = 'PS' AND customer_id != parent_account_id  THEN 'Y'
 12              WHEN ps = 'PS' THEN 'N'
 13         END "Supp"
 14    FROM (SELECT m.*,
 15                 CASE WHEN customer_id = parent_account_id
 16                       AND COUNT(*) OVER (PARTITION BY parent_account_id) = 1
 17                      THEN 'NonPS'
 18                      ELSE 'PS'
 19                 END ps
 20            FROM myData m) v;

CUSTOMER_ID ACCOUNT_ID PARENT_ACCOUNT_ID PS    P S
----------- ---------- ----------------- ----- - -
    3780952    3780952           3780952 NonPS
    3781004    3781004           3780997 PS    N Y
    3780997    3780997           3780997 PS    Y N

aF. · Accepted Answer · 2012-01-17 11:19:39Z

You can do it like this:

select a.Customer_ID, a.Account_ID, a.Parent_Account_ID,
b.PS as 'PS/NonPS',
case when (b.PS = 'NonPs') then NULL else
  (case when (a.Customer_ID = a.Parent_Account_ID) then 'Y' else 'N' end)
end as 'Principle',
case when (b.PS = 'NonPs') then NULL else
  (case when (a.Customer_ID = a.Parent_Account_ID) then 'N' else 'Y' end)
end as 'supp'

from Account a
inner join (
select a.Customer_ID, a.Account_ID, a.Parent_Account_ID,
case when (a.Customer_ID = a.Parent_Account_ID)
  and (select count(ax.Account_Id) from Account ax where ax.Parent_Account_ID = a.Parent_Account_ID) = 1
   then 'NonPS'
   else 'Ps'
 end as 'PS'
 from Account a) b on a.Customer_ID = b.Customer_ID and a.Account_ID = b.Account_ID and a.Parent_Account_ID = b.Parent_Account_ID

Collectives™ on Stack Overflow

ORACLE SQL Query with Parent and Child structure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related