Solved

Split query results from one row to 5 rows

Posted on 2014-04-04
10
626 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

813 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

12 Experts available now in Live!

Get 1:1 Help Now