Solved

Split query results from one row to 5 rows

Posted on 2014-04-04
10
619 Views
Last Modified: 2014-04-04
Hi,
How can I change the below SQL returning one row showing all 5 aging values
ACCOUNT_NUMBER, PARTY_NAME, "0-30", "31-60", "61-90", "91-120", ">120"

 into 5 rows showing one age range each row?
 ACCOUNT_NUMBER, PARTY_NAME, "0-30"
 ACCOUNT_NUMBER, PARTY_NAME,  "31-60"
 ACCOUNT_NUMBER, PARTY_NAME,  "61-90"
 ACCOUNT_NUMBER, PARTY_NAME,  "91-120"
 ACCOUNT_NUMBER, PARTY_NAME,  ">120"

select 
  "ACCOUNT_NUMBER",
 "PARTY_NAME",
 "0-30",
 "31-60",
 "61-90",
 "91-120",
 ">120"
from AR_AGING_SUM_VW 
where CUST_ACCOUNT_ID = 1234 

Open in new window

0
Comment
Question by:bcarlis
  • 6
  • 3
10 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 100 total points
ID: 39978770
You need to use  a function like PIVOT/UNPIVOT:
http://www.oracle-developer.net/display.php?id=506
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39978817
by any chance, is your data already unpivoted in the tables underlying the view?

can you use them and simply group by the criteria you need?

what I'm getting at is you might have a query that is doing a pivot just so you can unpivot when done.  That's clearly not efficient.
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39978825
Good point Sean.. I'll check
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
ID: 39978828
If you can't use the underlying tables, or if they are not an unpivoted source then try this....  (should work any version 9i or later.  Essentially the same as UNPIVOT but that requires 11g)


SELECT account_number,
       party_name,
       CASE WHEN n = 150 THEN '>120' ELSE (n - 30) || '-' || n END agerange,
       CASE
           WHEN n = 1 THEN "0-30"
           WHEN n = 2 THEN "31-60"
           WHEN n = 3 THEN "61-90"
           WHEN n = 4 THEN "91-120"
           ELSE ">120"
       END
           age
  FROM (SELECT "ACCOUNT_NUMBER",
               "PARTY_NAME",
               "0-30",
               "31-60",
               "61-90",
               "91-120",
               ">120"
          FROM ar_aging_sum_vw,
               (    SELECT LEVEL * 30 n
                      FROM DUAL
                CONNECT BY LEVEL <= 5)
         WHERE cust_account_id = 1234)
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39978832
Wow, it is a bunch of case statements

select trx_number,due_date,CUSTOMER_ID,
       aging_between_0_30 "0-30",
       aging_between_31_60 "31-60",
       aging_between_61_90 "61-90",
       aging_between_91_120 "91-120",
       aging_greater_than_120 ">120",
       ((aging_between_0_30) + (aging_between_31_60) + ((aging_between_61_90) + (aging_between_91_120) + (aging_greater_than_120))) Total
       from (select trx_number,due_date,CUSTOMER_ID, sum(aging_between_0_30) aging_between_0_30,
               sum(aging_between_31_60) aging_between_31_60,
               sum(aging_between_61_90) aging_between_61_90,
               sum(aging_between_91_120) aging_between_91_120,
               sum(aging_greater_than_120) aging_greater_than_120        
          FROM (SELECT rcta.trx_number,apsa.due_date,apsa.CUSTOMER_ID,
                   (CASE
                         WHEN (SYSDATE - apsa.due_date) BETWEEN -100 AND 30 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_between_0_30,                       
                       (CASE
                         WHEN (SYSDATE - apsa.due_date) between 31 AND 60 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_between_31_60,                       
                       (CASE
                         WHEN (SYSDATE - apsa.due_date) between 61 AND 90 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_between_61_90,                       
                       (CASE
                         WHEN (SYSDATE - apsa.due_date) between 91 and 120 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_between_91_120,                       
                       (CASE
                         WHEN (SYSDATE - apsa.due_date) > 120 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_greater_than_120                     
                  from ra_customer_trx_all       rcta,
                       ar_payment_schedules_all  apsa
                 WHERE apsa.customer_trx_id      = rcta.customer_trx_id
                 --AND   rcta.bill_to_customer_id  = 3347
                 ) GROUP BY trx_number,due_date,CUSTOMER_ID)
                 WHERE ((aging_between_0_30) + (aging_between_31_60) + ((aging_between_61_90) + (aging_between_91_120) + (aging_greater_than_120))) > 0
                 ORDER BY 8 DESC;

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 73

Expert Comment

by:sdstuber
ID: 39978844
can you use the raw data or are you required (either by business rule or privileges) to use the view?

can we assume this?

trx_number = account_number
CUSTOMER_ID = party_name
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39978845
sorry wrong view
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39978856
no that was right.. :)
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39978865
Beautiful...

SELECT account_number,
       PARTY_NAME,
       CASE WHEN n = 150 THEN '>120' ELSE (n - 30) || '-' || n END agerange,
       CASE
           WHEN n = 30 THEN "0-30"
           WHEN n = 60 THEN "31-60"
           WHEN n = 90 THEN "61-90"
           WHEN n = 120 THEN "91-120"
           ELSE ">120"
       END
           age
  FROM (SELECT "ACCOUNT_NUMBER",
               "PARTY_NAME",
               "0-30",
               "31-60",
               "61-90",
               "91-120",
               ">120", n
-- select * 
          FROM "APPS"."XXAPEX_DB_05_AR_AGING_SUM",
               (    SELECT LEVEL * 30 n
                      FROM DUAL
                CONNECT BY LEVEL <= 5)
         WHERE CUST_ACCOUNT_ID = 1007)

Open in new window

0
 
LVL 2

Author Closing Comment

by:bcarlis
ID: 39978871
Thank you Sean... works nice..

Thank you too lcohan...
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now